topic Re: Summing a Metric Based on Past (Conditional) Dates in SAS Programming

Summing a Metric Based on Past (Conditional) Dates

GreenMan1972 — Mon, 28 Apr 2014 22:50:24 GMT

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

Re: Summing a Metric Based on Past (Conditional) Dates

Reeza — Tue, 29 Apr 2014 00:43:19 GMT

Do you have the SAS ETS license?

If so, look into proc expand.

Re: Summing a Metric Based on Past (Conditional) Dates

GreenMan1972 — Tue, 29 Apr 2014 01:07:11 GMT

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

Re: Summing a Metric Based on Past (Conditional) Dates

Reeza — Tue, 29 Apr 2014 01:24:31 GMT

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;

Re: Summing a Metric Based on Past (Conditional) Dates

Haikuo — Tue, 29 Apr 2014 02:13:28 GMT

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

Re: Summing a Metric Based on Past (Conditional) Dates

GreenMan1972 — Tue, 29 Apr 2014 22:38:16 GMT

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

Re: Summing a Metric Based on Past (Conditional) Dates

GreenMan1972 — Tue, 29 Apr 2014 23:50:32 GMT

This is brilliant - works prefectly!

Many thanks to you both - hugely appreciated 🙂