SUMIF with multiple conditions

[sasvader]

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

[Reeza]

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;

[ballardw]

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.