## Rolling 24 months data

Frequent Contributor
Posts: 131

# 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: 23,721

## 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: 131

## 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: 23,721

## 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;

Discussion stats
• 3 replies
• 300 views
• 0 likes
• 2 in conversation