DATA Step, Macro, Functions and more

Summing a Metric Based on Past (Conditional) Dates

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Summing a Metric Based on Past (Conditional) Dates


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


Accepted Solutions
Solution
‎04-28-2014 10:13 PM
Respected Advisor
Posts: 3,156

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

Posted in reply to GreenMan1972

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

View solution in original post


All Replies
Super User
Posts: 19,789

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

Posted in reply to GreenMan1972

Do you have the SAS ETS license?

If so, look into proc expand.

New Contributor
Posts: 4

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

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

Super User
Posts: 19,789

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

Posted in reply to GreenMan1972

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;

New Contributor
Posts: 4

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

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

Solution
‎04-28-2014 10:13 PM
Respected Advisor
Posts: 3,156

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

Posted in reply to GreenMan1972

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

New Contributor
Posts: 4

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

This is brilliant - works prefectly!

Many thanks to you both - hugely appreciated :-)

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 304 views
  • 3 likes
  • 3 in conversation