topic Re: Rolling 24 months data in SAS Programming

Rolling 24 months data

pp2014 — Tue, 01 Apr 2014 19:15:39 GMT

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

Re: Rolling 24 months data

Reeza — Tue, 01 Apr 2014 19:37:19 GMT

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;

Re: Rolling 24 months data

pp2014 — Tue, 01 Apr 2014 21:25:36 GMT

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...

Re: Rolling 24 months data

Reeza — Tue, 01 Apr 2014 21:36:37 GMT

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;