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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.