Sum function in Proc SQL

[avatar]

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

SAS server running on AIX

Thanks.

[Tom]

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?

[avatar]

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

[bnarang]

Hi

Can you provide some sample data?

[Reeza · Accepted solution]

How is it not adding correctly?

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

[avatar]

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

[Reeza]

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;