BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
avatar
Fluorite | Level 6

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

SAS server running on AIX

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Tom
Super User Tom
Super User

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
Fluorite | Level 6

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
bnarang
Calcite | Level 5

Hi

Can you provide some sample data?

Reeza
Super User

How is it not adding correctly?

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

avatar
Fluorite | Level 6

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

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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