topic Re: Conditional sum in SAS Programming

Conditional sum

dapenDaniel — Tue, 26 May 2020 03:09:28 GMT

Hello,

I have a dataset like below.

data have;
input firmid $ Year indicator value;
datalines;
1001 1991  1  2
1001 1991  0  3
1001 1991  0  4
1001 1991  1  5
1002 2005  1  11
1002 2005  1  10
1002 2006  0  13
1002 2006  1  14
1002 2007  1  20
1002 2007  1  5
1002 2007  0  3
;
run;

I would like to calculate the sum for indicator is 1 and sum for indicator is 0 by firmid, year.

The expected output is below.

data want;
input firmid $ Year value1 value0;
datalines;
1001 1991  7  7
1002 2005  21  0
1002 2006  14  13
1002 2007  25  3
;
run;

What code do I need to use? Thanks.

Re: Conditional sum

PGStats — Tue, 26 May 2020 03:13:52 GMT

proc sql;
create table want as
select
    firmId,
    year,
    sum(case when indicator=1 then value else 0 end) as value1,    
    sum(case when indicator=0 then value else 0 end) as value0
from have
group by firmId, year;
quit;

Re: Conditional sum

dapenDaniel — Tue, 26 May 2020 03:21:31 GMT

Hi @PGStats ! Thanks for your help.