Summing in Proc SQL??? 1

[RookieDBO]

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

 

[achau]

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.

 

[RookieDBO]

Great! Thanks.

RookieDBO

 

[cruel]

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

 

[minken]

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.

 

[RookieDBO]

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

 

[mycotropic]

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