topic Re: sum up values across observation by multiple conditions in SAS Procedures

sum up values across observation by multiple conditions

ssulli11 — Thu, 14 Feb 2019 03:11:51 GMT

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

Re: sum up values across observation by multiple conditions

Reeza — Thu, 14 Feb 2019 03:53:14 GMT

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

Re: sum up values across observation by multiple conditions

PGStats — Thu, 14 Feb 2019 03:54:02 GMT

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;

Re: sum up values across observation by multiple conditions

mkeintz — Thu, 14 Feb 2019 04:01:03 GMT

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.

Re: sum up values across observation by multiple conditions

PGStats — Thu, 14 Feb 2019 04:11:12 GMT

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;

Re: sum up values across observation by multiple conditions

Ksharp — Thu, 14 Feb 2019 12:28:32 GMT

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;

Re: sum up values across observation by multiple conditions

ssulli11 — Mon, 18 Feb 2019 01:37:25 GMT

Thank you to everyone who responded and helped!!