Help using Base SAS procedures

SUMIF with multiple conditions

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

SUMIF with multiple conditions

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.

Accepted Solutions
Solution
‎08-21-2015 01:57 AM
Super User
Posts: 10,044

Re: SUMIF with multiple conditions

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


All Replies
Solution
‎08-21-2015 01:57 AM
Super User
Posts: 10,044

Re: SUMIF with multiple conditions

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

Occasional Contributor
Posts: 9

Re: SUMIF with multiple conditions

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 2694 views
  • 0 likes
  • 2 in conversation