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 |
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;
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.