topic Re: Sum function in Proc SQL in SAS Enterprise Guide

Sum function in Proc SQL

avatar — Thu, 27 Dec 2012 17:04:23 GMT

I am trying sum function in proc sql. It does not add up negative numbers correctly. any suggestions?

SAS server running on AIX

Thanks.

Re: Sum function in Proc SQL

Tom — Thu, 27 Dec 2012 17:08:07 GMT

Can you provide an example?

Is it possible you have numeric overflow? How many observations are you summing?

Did you try using PROC MEANS/SUMMARY instead?

Re: Sum function in Proc SQL

avatar — Thu, 27 Dec 2012 17:44:35 GMT

PROC SQL;

CREATE TABLE WORK.PTNT_PAYOR AS SELECT DISTINCT E.SPCLTY_PHMCY_PHMCY_ID,

datepart(E.ORDR_HDR_FILL_DT) format=mmddyy10. as FILL_DATE,

E.RX_RX_NB AS PRESCRIPTION_NUMBER,

E.PTNT_ID AS PATIENT_ID,

E.PTNT_FIRST_NM,

E.PTNT_LAST_NM,

E.ORDR_HDR_ORDR_TYPE,

E.ORDR_DTL_DRUG_DSPNS_QTY,

SUM(E.ORDR_DTL_DRUG_DSPNS_QTY) FORMAT=8. AS TOTAL_QTY,

UPCASE(E.PRODUCT_DESC_1) AS PRODUCT_DESC

FROM SASUSER.SPCLTY_PTNT_DETAIL E

WHERE E.PAYOR_LVL_1_LOW_ID IN('IBMSHAZ1C','PENNSPA2C','NQR-NQRH','WELLSFA1C','FEPPBM01C')

GROUP BY E.SPCLTY_PHMCY_PHMCY_ID, E.PTNT_ID

ORDER BY E.SPCLTY_PHMCY_PHMCY_ID, E.PTNT_ID;

QUIT;

There are 300 records in the file. I did not try proc means yet. will give it a try

Thanks

Re: Sum function in Proc SQL

bnarang — Thu, 27 Dec 2012 17:53:35 GMT

Can you provide some sample data?

Re: Sum function in Proc SQL

Reeza — Thu, 27 Dec 2012 18:00:00 GMT

How is it not adding correctly?

Can you show the data you have and where you think the numbers are wrong?

Re: Sum function in Proc SQL

avatar — Thu, 27 Dec 2012 18:02:51 GMT

If there are 4 records for patient_id = ADACI61 like -8,-8,-8,-8 total shows up as 52

Re: Sum function in Proc SQL

Reeza — Thu, 27 Dec 2012 18:15:19 GMT

So you're saying the following returns 4 records with all -8 for qty?

PROC SQL;

CREATE TABLE WORK.PTNT_PAYOR AS

SELECT DISTINCT E.SPCLTY_PHMCY_PHMCY_ID,

E.RX_RX_NB AS PRESCRIPTION_NUMBER,

E.PTNT_ID AS PATIENT_ID,

E.ORDR_DTL_DRUG_DSPNS_QTY,

UPCASE(E.PRODUCT_DESC_1) AS PRODUCT_DESC

FROM SASUSER.SPCLTY_PTNT_DETAIL E

WHERE E.PAYOR_LVL_1_LOW_ID IN('IBMSHAZ1C','PENNSPA2C','NQR-NQRH','WELLSFA1C','FEPPBM01C')

and e.ptnt_id='ADACI61'

ORDER BY E.SPCLTY_PHMCY_PHMCY_ID, E.PTNT_ID;

QUIT;