Hi all,
I am trying to create the output variable 'Want' using an Exel-equivalent SUMIF function with multiple conditions.
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 | 0 | . |
14/01/2014 | A1 | 1 | 0 | 300 |
15/01/2014 | A1 | 0 | 0 | . |
30/12/2013 | B1 | 0 | 100 | . |
31/12/2013 | B1 | 1 | 0 | 100 |
1/01/2014 | B1 | 0 | 0 | . |
2/01/2014 | B1 | 0 | 0 | . |
3/01/2014 | B1 | 0 | 200 | . |
4/01/2014 | B1 | 0 | 0 | . |
5/01/2014 | B1 | 0 | 0 | . |
6/01/2014 | B1 | 0 | 0 | . |
7/01/2014 | B1 | 0 | 0 | . |
8/01/2014 | B1 | 0 | 300 | . |
9/01/2014 | B1 | 0 | 0 | . |
10/01/2014 | B1 | 0 | 0 | . |
11/01/2014 | B1 | 0 | 400 | . |
12/01/2014 | B1 | 0 | 0 | . |
13/01/2014 | B1 | 0 | 0 | . |
14/01/2014 | B1 | 1 | 0 | 700 |
15/01/2014 | B1 | 0 | 0 | . |
data have; input Date : ddmmyy12. ID $ Indicator Have Want; format Date ddmmyy10.; drop want; cards; 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 0 . 14/01/2014 A1 1 0 300 15/01/2014 A1 0 0 . 30/12/2013 B1 0 100 . 31/12/2013 B1 1 0 100 1/01/2014 B1 0 0 . 2/01/2014 B1 0 0 . 3/01/2014 B1 0 200 . 4/01/2014 B1 0 0 . 5/01/2014 B1 0 0 . 6/01/2014 B1 0 0 . 7/01/2014 B1 0 0 . 8/01/2014 B1 0 300 . 9/01/2014 B1 0 0 . 10/01/2014 B1 0 0 . 11/01/2014 B1 0 400 . 12/01/2014 B1 0 0 . 13/01/2014 B1 0 0 . 14/01/2014 B1 1 0 700 15/01/2014 B1 0 0 . ; run; proc sql; create table want as select *,case when Indicator=1 then select sum(Have) from have where ID=a.ID and Date between a.date-7 and a.date else . end as want from have as a; quit;
Xia Keshan
data have; input Date : ddmmyy12. ID $ Indicator Have Want; format Date ddmmyy10.; drop want; cards; 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 0 . 14/01/2014 A1 1 0 300 15/01/2014 A1 0 0 . 30/12/2013 B1 0 100 . 31/12/2013 B1 1 0 100 1/01/2014 B1 0 0 . 2/01/2014 B1 0 0 . 3/01/2014 B1 0 200 . 4/01/2014 B1 0 0 . 5/01/2014 B1 0 0 . 6/01/2014 B1 0 0 . 7/01/2014 B1 0 0 . 8/01/2014 B1 0 300 . 9/01/2014 B1 0 0 . 10/01/2014 B1 0 0 . 11/01/2014 B1 0 400 . 12/01/2014 B1 0 0 . 13/01/2014 B1 0 0 . 14/01/2014 B1 1 0 700 15/01/2014 B1 0 0 . ; run; proc sql; create table want as select *,case when Indicator=1 then select sum(Have) from have where ID=a.ID and Date between a.date-7 and a.date else . end as want from have as a; quit;
Xia Keshan
Thank you Keshan. I can confirm that the code works.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.