Rolling 24 months data

Reply
Frequent Contributor
Posts: 124

Rolling 24 months data

I have rolling 24 months  data as follows:

Mth1-Mth24  with numeric values at ID level.

Mth24  is the most current month.

I want  the  Current  Years  YTD(CY_YTD)   and Prev. Years YTD (PY_YTD)  values based on the  current month.

I am using the logic as below.  Is there any   better way to do this?

th rolling 24

%let curdate = today();

Format  mnth $2.;

Mnth = put(month(&curdate.) ,z2.) ;

If  Mnth = “01”  then do;

   CY_YTD = Mth24;

   PY_YTD = Mth12; end;

If  Mnth = “02”  then do;

   CY_YTD = sum(of Mth23-Mth24);

   PY_YTD = sum(of Mth11-Mth12); end;

If  Mnth = “03”  then do;

   CY_YTD = sum(of Mth22-Mth24);

   PY_YTD = sum(of Mth10-Mth12); end;

And so on

Super User
Posts: 17,963

Re: Rolling 24 months data

Can you post some sample data.

I'm not sure how your data is layed out, but if it is 2 years back to to back, you can consider declaring two arrays.

array current_year(12) month13-month24;

array prev_year(12) month1-month12;

month=month(today());

do i=12 to (12-month) by -1;;

     current_YTD=current_YTD+current_year(i);

     previous_YTD=previous_YTD+prev_year(i);

end;

Frequent Contributor
Posts: 124

Re: Rolling 24 months data

Sample data is as follows:

data have;

  input id $2. mth1-mth24 2.;

  cards;

10 2 4 5 8 9 13 4 6 7 9 8 2 5 7 6 4 6 8 9 5 2 6 11 8

12 5 6 8 4 2 12 5 6 8 6 8 2 5 7 6 4 5 8 9 5 2 4 16 2

Mth24 is the latest month (Mar 2014). Mth23 will be Feb 2014, Mth22 will be Jan 2014 and so on...

Super User
Posts: 17,963

Re: Rolling 24 months data

This seems to work, but today is April 1st, so month4 actually.

If you're going to specify the month then you'll need to modify the code according rather than using today's date function.

data have;

  input id $2. mth1-mth24 2.;

  cards;

10 2 4 5 8 9 13 4 6 7 9 8 2 5 7 6 4 6 8 9 5 2 6 11 8

12 5 6 8 4 2 12 5 6 8 6 8 2 5 7 6 4 5 8 9 5 2 4 16 2

;

run;

*let date='31Mar2014'd;

data want;

set have;

array current_year(12) mth24-mth13;

array prev_year(12) mth12-mth1;

month=month(today());

*month=month(&date.);

current_YTD=0;

previous_YTD=0;

do i=1 to month;

     current_YTD=current_YTD+current_year(i);

     previous_YTD=previous_YTD+prev_year(i);

     put i;

end;

run;

Ask a Question
Discussion stats
  • 3 replies
  • 239 views
  • 0 likes
  • 2 in conversation