topic Re: SUMIF with multiple conditions in SAS Procedures

SUMIF with multiple conditions

sasvader — Thu, 20 Aug 2015 04:36:47 GMT

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	.

Re: SUMIF with multiple conditions

Ksharp — Fri, 21 Aug 2015 05:57:10 GMT

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

Re: SUMIF with multiple conditions

sasvader — Sun, 23 Aug 2015 22:39:03 GMT

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