Help using Base SAS procedures

SUMIF with multiple conditions

Reply
Occasional Contributor
Posts: 9

SUMIF with multiple conditions

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.

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/2014A10..
14/01/2014A11.300
15/01/2014A10..
30/12/2013B10100.
31/12/2013B11.100
1/01/2014B10..
2/01/2014B10..
3/01/2014B10200.
4/01/2014B10..
5/01/2014B10..
6/01/2014B10..
7/01/2014B10..
8/01/2014B10300.
9/01/2014B10..
10/01/2014B10..
11/01/2014B10200.
11/01/2014B10400.
12/01/2014B10..
13/01/2014B10..
14/01/2014B11.900
Super User
Posts: 17,784

Re: SUMIF with multiple conditions

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;

Super User
Posts: 10,483

Re: SUMIF with multiple conditions

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.

Ask a Question
Discussion stats
  • 2 replies
  • 511 views
  • 0 likes
  • 3 in conversation