Alex Alex - 2 days ago 5
SQL Question

SAS/SQL sum amounts distinctly for each group by object?

I have the following code:

proc sql;
CREATE TABLE temp AS
(SELECT asofdt,
SUM(CASE WHEN trans_state ='cur_cur' THEN 1 ELSE 0 END) AS _cur_cur,
SUM(CASE WHEN trans_state ='cur_worse' THEN 1 ELSE 0 END) AS _cur_worse,
SUM(CASE WHEN trans_state ='cur_pre' THEN 1 ELSE 0 END) AS _cur_pre,
SUM(CASE WHEN trans_state ='30_better' THEN 1 ELSE 0 END) AS _30_better,
SUM(CASE WHEN trans_state ='30_30' THEN 1 ELSE 0 END) AS _30_30,
SUM(CASE WHEN trans_state ='60_90' THEN 1 ELSE 0 END) AS _60_90
FROM PERFORMANCE_TRANS_STATES_CLEAN
GROUP BY asofdt);
run;


The problem is it is adding the value from the previous group by asofdt onto the next one. So it is a cumulative sum as I go down the group bys. I would like the sum to be specific to each group by object. Any ideas on how?

Here's a picture of my output.

enter image description here

Answer

Your program seems fine to me. I reproduced it below with fewer observations and did not find that the total was cumulative.

data df;
input asofdt MMDDYY8. trans_state $;
datalines;
01/01/16 cur_cur
01/02/16 cur_pre
01/02/16 cur_pre
01/02/16 cur_cur
01/03/16 cur_pre
;
run;

proc sql;
CREATE TABLE temp AS 
(SELECT asofdt,
        SUM(CASE WHEN trans_state ='cur_cur' THEN 1 ELSE 0 END) AS _cur_cur,
        SUM(CASE WHEN trans_state ='cur_worse' THEN 1 ELSE 0 END) AS _cur_worse,
        SUM(CASE WHEN trans_state ='cur_pre' THEN 1 ELSE 0 END) AS _cur_pre,
        SUM(CASE WHEN trans_state ='30_better' THEN 1 ELSE 0 END) AS _30_better,
        SUM(CASE WHEN trans_state ='30_30' THEN 1 ELSE 0 END) AS _30_30,
        SUM(CASE WHEN trans_state ='60_90' THEN 1 ELSE 0 END) AS _60_90
FROM df
GROUP BY asofdt);
quit;

enter image description here

Comments