Good afternoon All!
I am attempting to sum a variable using multiple conditions. See below for the data I have and want. I want to create two new variables that sum "supply" by "ID" based on "year" and "AorB" group. I am aware of how to complete this using proc sql when looking at only two other variables (ID and Year), but not sure how to complete this data step when using multiple conditions. Your help and expertise is always appreciated!!
Data Have:
ID | AorB | Year | Supply |
15169 | A | 2009 | 30 |
15169 | A | 2009 | 60 |
15169 | A | 2009 | 30 |
15169 | A | 2009 | 90 |
15169 | A | 2010 | 30 |
15169 | A | 2010 | 30 |
15169 | B | 2010 | 30 |
15169 | A | 2010 | 30 |
30629 | A | 2009 | 90 |
30629 | A | 2009 | 90 |
30629 | A | 2009 | 30 |
30629 | B | 2009 | 30 |
30629 | B | 2009 | 30 |
30629 | A | 2010 | 90 |
30629 | A | 2011 | 90 |
Data Want:
ID | AorB | Year | Supply | Year_Supply_A | Year_Supply_B |
15169 | A | 2009 | 30 | 210 | 0 |
15169 | A | 2009 | 60 | 210 | 0 |
15169 | A | 2009 | 30 | 210 | 0 |
15169 | A | 2009 | 90 | 210 | 0 |
15169 | A | 2010 | 30 | 90 | 30 |
15169 | A | 2010 | 30 | 90 | 30 |
15169 | B | 2010 | 30 | 90 | 30 |
15169 | A | 2010 | 30 | 90 | 30 |
30629 | A | 2009 | 90 | 210 | 60 |
30629 | A | 2009 | 90 | 210 | 60 |
30629 | A | 2009 | 30 | 210 | 60 |
30629 | B | 2009 | 30 | 210 | 60 |
30629 | B | 2009 | 30 | 210 | 60 |
30629 | A | 2010 | 90 | 90 | 0 |
30629 | A | 2011 | 90 | 90 | 0 |
Use proc sql, and take advantage of auto remerge:
proc sql;
create table want as
select
*,
sum(case when AorB = "A" then Supply else 0 end) as Year_Supply_A,
sum(case when AorB = "B" then Supply else 0 end) as Year_Supply_B
from have
group by ID, Year;
quit;
There's probably a quicker way than this, but this is dynamic until the last step where I'm replacing the zeros. There are ways to do that if necessary.
1. Summarize data
2. Transpose into different columns
3. Merge into main data
4. Replace missing with 0
data have;
input ID $ AorB $ Year Supply;
cards;
15169 A 2009 30
15169 A 2009 60
15169 A 2009 30
15169 A 2009 90
15169 A 2010 30
15169 A 2010 30
15169 B 2010 30
15169 A 2010 30
30629 A 2009 90
30629 A 2009 90
30629 A 2009 30
30629 B 2009 30
30629 B 2009 30
30629 A 2010 90
30629 A 2011 90
;;;;
run;
proc means data=have noprint nway;
class ID Year AorB;
var Supply;
output out=summary sum=Total;
run;
proc transpose data=summary out=summary_wide prefix=Supply;
by ID Year;
ID AorB;
var Total;
run;
proc sql;
create table want as
select a.*, coalesce(SupplyA, 0) as SupplyA, coalesce(SupplyB, 0) as SupplyB
from have as a
left join summary_wide as b
on a.ID=b.ID and a.Year=b.Year;
quit;
@ssulli11 wrote:
Good afternoon All!
I am attempting to sum a variable using multiple conditions. See below for the data I have and want. I want to create two new variables that sum "supply" by "ID" based on "year" and "AorB" group. I am aware of how to complete this using proc sql when looking at only two other variables (ID and Year), but not sure how to complete this data step when using multiple conditions. Your help and expertise is always appreciated!!
Data Have:
ID AorB Year Supply 15169 A 2009 30 15169 A 2009 60 15169 A 2009 30 15169 A 2009 90 15169 A 2010 30 15169 A 2010 30 15169 B 2010 30 15169 A 2010 30 30629 A 2009 90 30629 A 2009 90 30629 A 2009 30 30629 B 2009 30 30629 B 2009 30 30629 A 2010 90 30629 A 2011 90
Data Want:
ID AorB Year Supply Year_Supply_A Year_Supply_B 15169 A 2009 30 210 0 15169 A 2009 60 210 0 15169 A 2009 30 210 0 15169 A 2009 90 210 0 15169 A 2010 30 90 30 15169 A 2010 30 90 30 15169 B 2010 30 90 30 15169 A 2010 30 90 30 30629 A 2009 90 210 60 30629 A 2009 90 210 60 30629 A 2009 30 210 60 30629 B 2009 30 210 60 30629 B 2009 30 210 60 30629 A 2010 90 90 0 30629 A 2011 90 90 0
Use proc sql, and take advantage of auto remerge:
proc sql;
create table want as
select
*,
sum(case when AorB = "A" then Supply else 0 end) as Year_Supply_A,
sum(case when AorB = "B" then Supply else 0 end) as Year_Supply_B
from have
group by ID, Year;
quit;
If your data are sorted by ID/YEAR, then you can make a single data step work:
data have;
input ID AorB :$1. Year Supply;
datalines;
15169 A 2009 30
15169 A 2009 60
15169 A 2009 30
15169 A 2009 90
15169 A 2010 30
15169 A 2010 30
15169 B 2010 30
15169 A 2010 30
30629 A 2009 90
30629 A 2009 90
30629 A 2009 30
30629 B 2009 30
30629 B 2009 30
30629 A 2010 90
30629 A 2011 90
run;
data want;
set have;
by id year;
if first.year then call missing(supply_a,supply_b);
if aorb='A' then supply_a+supply; else
if aorb='B' then supply_b+supply;
if last.year;
supply_a=sum(0,supply_a);
supply_b=sum(0,supply_b);
do until (last.year);
set have;
by id year;
output;
end;
run;
Notice that supply_a and supplly_b are aggregated until encountering the last obs for a given id/year. The those obs are then re-read and output with the correct supply_a and supply_b values.
I prefer the logically simpler (IMHO):
data want;
supply_a = 0;
supply_b = 0;
do until (last.year);
set have; by id year;
if aorb='A' then supply_a = supply_a + supply;
if aorb='B' then supply_b = supply_b + supply;
end;
do until (last.year);
set have; by id year;
output;
end;
run;
Similar with PG's code.
data have;
input ID $ AorB $ Year Supply;
cards;
15169 A 2009 30
15169 A 2009 60
15169 A 2009 30
15169 A 2009 90
15169 A 2010 30
15169 A 2010 30
15169 B 2010 30
15169 A 2010 30
30629 A 2009 90
30629 A 2009 90
30629 A 2009 30
30629 B 2009 30
30629 B 2009 30
30629 A 2010 90
30629 A 2011 90
;;;;
run;
proc sql;
select
*,
coalesce((select sum(Supply) from have where id=a.id and year=a.year and AorB = "A"),0) as Year_Supply_A,
coalesce((select sum(Supply) from have where id=a.id and year=a.year and AorB = "B"),0) as Year_Supply_B
from have as a;
quit;
Thank you to everyone who responded and helped!!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.