SUMIF with multiple conditions

sasvader — Thu, 20 Aug 2015 04:51:52 GMT

Hi all,

Would greatly appreciate your help with this problem.

I am trying to create the output variable Want using an Excel-equivalent SUMIF function with multiple conditions.

Condition 1: Want is the sum of Have within the past 7 days of an observation where Indicator = 1 e.g. the 14th Jan observation for Want would be 200 + 100 = 300. The 500 from 6th Jan is ignored because it's more than seven days ago.

Condition 2: The data has to be ID specific e.g. the Want for a B1 observation should not include the Have for A1 observations

Note: There could be multiple observations for a single date e.g. 11th Jan has two observations with two separate Have (200 and 400).

Thank you very much in advance.

Sample data below:

My actual data set has Date in SAS format.

Date	ID	Indicator	Have	Want
30/12/2013	A1	0	.	.
31/12/2013	A1	0	.	.
1/01/2014	A1	0	.	.
2/01/2014	A1	0	.	.
3/01/2014	A1	0	.	.
4/01/2014	A1	0	.	.
5/01/2014	A1	0	.	.
6/01/2014	A1	0	500	.
7/01/2014	A1	0	.	.
8/01/2014	A1	0	200	.
9/01/2014	A1	0	.	.
10/01/2014	A1	0	.	.
11/01/2014	A1	0	.	.
12/01/2014	A1	0	100	.
13/01/2014	A1	0	.	.
14/01/2014	A1	1	.	300
15/01/2014	A1	0	.	.
30/12/2013	B1	0	100	.
31/12/2013	B1	1	.	100
1/01/2014	B1	0	.	.
2/01/2014	B1	0	.	.
3/01/2014	B1	0	200	.
4/01/2014	B1	0	.	.
5/01/2014	B1	0	.	.
6/01/2014	B1	0	.	.
7/01/2014	B1	0	.	.
8/01/2014	B1	0	300	.
9/01/2014	B1	0	.	.
10/01/2014	B1	0	.	.
11/01/2014	B1	0	200	.
11/01/2014	B1	0	400	.
12/01/2014	B1	0	.	.
13/01/2014	B1	0	.	.
14/01/2014	B1	1	.	900

Re: SUMIF with multiple conditions

Reeza — Thu, 20 Aug 2015 19:57:46 GMT

Use a SQL self join -this is untested and probably doesn't work but should give you the idea.

proc sql;

create table want as

select a.*, case when a.indicator=1 then sum(b.have)

else .

end as sum

from have as a

left join have as b

on a.id=b.id

and (b.date-a.date)<7;

quit;

Re: SUMIF with multiple conditions

ballardw — Thu, 20 Aug 2015 20:37:01 GMT

Also, are your date values SAS date values or character? If character then the first thing will be getting SAS dates so you can use actual date comparisons.

topic Re: SUMIF with multiple conditions in SAS Procedures

SUMIF with multiple conditions

Re: SUMIF with multiple conditions

Re: SUMIF with multiple conditions