BookmarkSubscribeRSS Feed
sasvader
Fluorite | Level 6

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

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;

ballardw
Super User

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.

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
  • 2279 views
  • 0 likes
  • 3 in conversation