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;
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.
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.