BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasvader
Fluorite | Level 6

Hi all,

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

DateIDIndicatorHaveWant
30/12/2013A10..
31/12/2013A10..
1/01/2014A10..
2/01/2014A10..
3/01/2014A10..
4/01/2014A10..
5/01/2014A10..
6/01/2014A10500.
7/01/2014A10..
8/01/2014A10200.
9/01/2014A10..
10/01/2014A10..
11/01/2014A10..
12/01/2014A10100.
13/01/2014A100.
14/01/2014A110300
15/01/2014A100.
30/12/2013B10100.
31/12/2013B110100
1/01/2014B100.
2/01/2014B100.
3/01/2014B10200.
4/01/2014B100.
5/01/2014B100.
6/01/2014B100.
7/01/2014B100.
8/01/2014B10300.
9/01/2014B100.
10/01/2014B100.
11/01/2014B10400.
12/01/2014B100.
13/01/2014B100.
14/01/2014B110700
15/01/2014B100.
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

View solution in original post

2 REPLIES 2
Ksharp
Super User
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

sasvader
Fluorite | Level 6

Thank you Keshan. I can confirm that the code works.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 11596 views
  • 3 likes
  • 2 in conversation