Desktop productivity for business analysts and programmers

Sum function in Proc SQL

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Sum function in Proc SQL

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

SAS server running on AIX

Thanks.


Accepted Solutions
Solution
‎12-27-2012 01:00 PM
Super User
Posts: 19,157

Re: Sum function in Proc SQL

How is it not adding correctly?

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

View solution in original post


All Replies
Super User
Super User
Posts: 6,845

Re: Sum function in Proc SQL

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?

Contributor
Posts: 66

Re: Sum function in Proc SQL

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


SumFunc.png
Frequent Contributor
Posts: 86

Re: Sum function in Proc SQL

Hi

Can you provide some sample data?

Solution
‎12-27-2012 01:00 PM
Super User
Posts: 19,157

Re: Sum function in Proc SQL

How is it not adding correctly?

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

Contributor
Posts: 66

Re: Sum function in Proc SQL

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

Super User
Posts: 19,157

Re: Sum function in Proc SQL

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 939 views
  • 0 likes
  • 4 in conversation