BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GreenMan1972
Calcite | Level 5


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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

6 REPLIES 6
Reeza
Super User

Do you have the SAS ETS license?

If so, look into proc expand.

GreenMan1972
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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
Calcite | Level 5

This is brilliant - works prefectly!

Many thanks to you both - hugely appreciated 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 929 views
  • 3 likes
  • 3 in conversation