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.
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;
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;
Hi @PGStats ! Thanks for your help.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.