Summing a Metric Based on Past (Conditional) Dates

[GreenMan1972]

Hi all,

I'd aprpeciate any help on how to solve this problem. My SAS data set has three variables - a unique (person) identifier, a date, and a metric I need to sum up (Points).

Below is some (dummy) sample data:

ID          Date                Points

111        15-Feb-2013    100

111         28-Mar-2013    200

111         17-Nov-2013    100

111         14-Apr-2014      50

111         18-May-2014    100

112          17-Jun-2012     250

112           04-Apr-2013    100

112           28-Aug-2013    100

Now what I need to produce is a data set that contains the same number of rows as the above data set, but sums up the Points retrospectively based on the past Date values. In other words what I need to do is cumulatively sum Points for every data row, but for all Dates that occur <= 12 months from the current value of Date. This would be a new variable, say Cumulative_12m_Points

Below is what I am trying to build based on the above data set:

ID          Date                Points    Cumulative_12m_Points

111        15-Feb-2013    100         100

111         28-Mar-2013    200         300

111         17-Nov-2013    100          400

111         14-Apr-2014      50          150

111         18-May-2014    100         250

112          17-Jun-2012     250        250

112           04-Apr-2013    100        350

112           28-Aug-2013    100        200

So the Cumulative_12m_Points variable sums up Points for the last 12 months based on Date (for each ID).

Any help appreciated!

Regards,

GreenMan1972

[Reeza]

Do you have the SAS ETS license?

If so, look into proc expand.

[GreenMan1972]

No unfortunately we do not. Am hoping in the absence of this there may be a data step or proc sql solution to this....

[Reeza]

There is a SQL solution.

Something like the following (untested).

You'll probably need to play around with the intck function to get what you want.

proc sql;

create table want as

select a.id, a.date, a.points, sum(b.points) as cum_points

from have as a

left join have as b

where intck('month', a.date, b.date)<=12

group by a.id, a.date, a.points;

quit;

[GreenMan1972]

Appreciate your help here. I will have a play around with this code!

[Haikuo · Accepted solution]

Echo with Reeza's approach, Proc SQL is indeed very handy to deal with this kind of moving sum, here is another alternative:

data have;

input ID$          Date :date11.                Points;

format date date11.;

cards;

111        15-Feb-2013    100

111         28-Mar-2013    200

111         17-Nov-2013    100

111         14-Apr-2014      50

111         18-May-2014    100

112          17-Jun-2012     250

112           04-Apr-2013    100

112           28-Aug-2013    100

;

proc sql;

create table want as

   select *, (select sum(points) from have where id=a.id and date between a.date and intnx('month',a.date,-12,'s')) as Cum_12m_points

      from have a;

quit;

Haikuo

[GreenMan1972]

This is brilliant - works prefectly!

Many thanks to you both - hugely appreciated 🙂