×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Summing in Proc SQL???

Summing in Proc SQL???

Summing in Proc SQL???

(OP)
I'm trying to extract data from an Oracle database using proc sql and sum statement.  I can do it fine without the sum statement but once I use the sum statement it doesn't.  Please help.  What's wrong with my code???


proc sql;
create table Practice.Shipment_Test as

select distinct
    WHLSL_CUST_BILL_DOC_VW.RTL_CO_NUM,
    WHLSL_CUST_BILL_DOC_ITEM_VW.SHIP_FROM_DC_NUM,
    WHLSL_CUST_BILL_DOC_ITEM_VW.NG_ITEM_NUM,
    WHLSL_CUST_BILL_DOC_ITEM_VW.UT_DESC,
    SUM(ORIG_ORD_QTY),
    SUM(SHIP_QTY )

from connection to odbc

(select distinct
    DWS.WHLSL_CUST_BILL_DOC_VW.RTL_CO_NUM,
    DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.SHIP_FROM_DC_NUM,
    DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.NG_ITEM_NUM,
    DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.UT_DESC,
    SUM(DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.ORIG_ORD_QTY),
    SUM(DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.SHIP_QTY)
from
    DWS.WHLSL_CUST_BILL_DOC_VW,
    DWS.WHLSL_CUST_BILL_DOC_ITEM_VW
where
    (
    (
            DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.BUS_DT =to_date('16-Oct-2001','dd-mm-yyyy')
    )
    )
    and
    DWS.WHLSL_CUST_BILL_DOC_VW.BILL_DOC_NUM =  
        DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.BILL_DOC_NUM
    and
    DWS.WHLSL_CUST_BILL_DOC_VW.ADJ_NUM =
        DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.ADJ_NUM
group by
    DWS.WHLSL_CUST_BILL_DOC_VW.RTL_CO_NUM,
    DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.SHIP_FROM_DC_NUM,
    DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.NG_ITEM_NUM,
    DWS.WHLSL_CUST_BILL_DOC_ITEM_VW.UT_DESC);
quit;
run;



Thanks,
RookieDBO

RE: Summing in Proc SQL???

I think you have to specify a name for your sums. Try

SUM(ORIG_ORD_QTY) AS SUM_ORIG,
SUM(SHIP_QTY) AS SUM_SHIP

in your PROC SQL.

RE: Summing in Proc SQL???

(OP)
Great! Thanks.

RookieDBO

RE: Summing in Proc SQL???

If you get the anwsers you want, then never mind.The thing with your SQL is that you have two sum f'ns above the statement from connection to ODBC   but do not have a group-by to go with them (though you have group-bys for the sums below).  Specify names as previous reply suggests (for all columns) and use those names in the select statement above the statement from connection to ODBC

RE: Summing in Proc SQL???

Hi,
Cruel is absolutely right. However, you are using SAS so why not use Proc Means or Proc Summary to calculate sums etc. I bet that would speed things up as well.

RE: Summing in Proc SQL???

(OP)
Are you guys SAS programmers?  Does anyone of you know a good SAS forum?

RE: Summing in Proc SQL???

The only good SAS resources I have ever found are the Manuals and the SAS website. Once you have a few years under your belt you can wade through the documention pretty quickly. Oh yah; and if it involves output other then the regular SAS text, formated as HTML, give up and buy MatLab or SigmaPlot cause SAS WON'T do good plots. Keep posting your questions though - I promise to help if it's near my field!

g

"Man is the cheapest computer we can put into a spacecraft and the only one we can mass-produce with unskilled labor."                             Werner von Braun

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close