I am trying sum function in proc sql. It does not add up negative numbers correctly. any suggestions?
SAS server running on AIX
Thanks.
How is it not adding correctly?
Can you show the data you have and where you think the numbers are wrong?
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?
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
Hi
Can you provide some sample data?
How is it not adding correctly?
Can you show the data you have and where you think the numbers are wrong?
If there are 4 records for patient_id = ADACI61 like -8,-8,-8,-8 total shows up as 52
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.