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
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;
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...
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.