Hi folks,
I have a question. I have numeric variables JAN14--DEC14, where 14 stands for 2014 (similar data for previous years. e.g,. JAN13--DEC13 for 2013). I want to create a change variable of last 3 month, previous 3 month, last 6 month, previous 6 month, last 12 month and previous 12 month. For example, if the current date is SEP14, then,
last3month would be JUL14--SEP14
previous3month would be APR14-JUN14
Same thing for the last 6, 12 months. I want to obtain some summary statistics like mean and standard deviation on these change variables.
However, as the current date would change, so I would like to write some macros to automate the process. Below are the macro code I wrote and I have obtained those variables that I want. However, they are now in the 114, 214, 314 format as I changed the months to numeric format to do calculate the last3,6,12 months. Now I want to change them back to the original format JAN14-DEC14 in order to be able to use those variables. I am not too sure how I can do this. Any suggestions?
/*write some macro to automate the process*/
%let JAN=1;
%let FEB=2;
%let MAR=3;
%let APR=4;
%let MAY=5;
%let JUN=6;
%let JUL=7;
%let AUG=8;
%let SEP=9;
%let OCT=10;
%let NOV=11;
%let DEC=12;
OPTIONS SYMBOLGEN;
/*only need to change the lates_date*/
%let latest_date=SEP14;
%let latest_year = %substr(&latest_date,4,2);
%let latest_month = %substr(&latest_date,1,3);
data changes(drop=check1 check2 check3);
/*obtain the last and previous 3 months from the latest date*/
/*two situations were considered. one is that the last/previous 3 months are from the same year
or across two years*/
check1=&&&latest_month-2;
if check1 > 0 then do;
call symput('last_3month',check1);
last_3=symget('last_3month')||left(&latest_year);
call symput('p_3month',check1-1);
p_3month_end=symget('p_3month')||left(&latest_year);
end;
else if check1 < 0 then do;
call symput('last_3month',check1+12);
last_3=symget('last_3month')||left(&latest_year-1);
call symput('p_3month',check1+11);
p_3month_end=symget('p_3month')||left(&latest_year-1);
end;
/*obtain the last and previous 6 months from the latest date*/
/*two situations were considered. one is that the last/previous 6 months are from the same year
or across two years*/
check2=&&&latest_month-5;
if check2 >0 then do;
call symput('last_6month',check2);
last_6=symget('last_6month')||left(&latest_year);
p_3month_start=last_6;
call symput('p_6month',check2-1);
p_6month_end=symget('p_6month')||left(&latest_year);
end;
else if check2 < 0 then do;
call symput('last_6month',check2+12);
last_6=symget('last_6month')||left(&latest_year-1);
p_3month_start=last_6;
call symput('p_6month',check2+11);
p_6month_end=symget('p_6month')||left(&latest_year-1);
end;
/*obtain the last and previous 12 months from the latest date*/
/*two situations were considered. one is that the last/previous 12 months are from the same year
or across two years*/
check3=&&&latest_month-11;
if check3 >0 then do;
call symput('last_12month',check3);
last_12=symget('last_12month')||left(&latest_year);
p_6month_start=last_12;
p_12month_start=symget('last_12month')||left(&latest_year-2);
call symput('p_12month',check3-1);
p_12month_end=symget('p_12month')||left(&latest_year);
end;
else if check3 < 0 then do;
call symput('last_12month',check3+12);
last_12=symget('last_12month')||left(&latest_year-1);
p_6month_start=last_12;
p_12month_start=symget('last_12month')||left(&latest_year-2);
call symput('p_12month',check3+11);
p_12month_end=symget('p_12month')||left(&latest_year-1);
end;
run;
Hi,
For the goal of creating the macro variables, INTNX works, you just need to use INPUTN to convert your MONYY date into a SAS date, and wrap those in %SYSFUNC if you want to stay in macro land:
27 %let latest_date=SEP14; 28 29 %let Last_3month=%sysfunc(intnx(month,%sysfunc(inputn(SEP14,monyy5.)),-3),monyy5.); 30 %put &Last_3month; JUN14
You could also make your own little macro function, e.g.:
32 %macro intnx(start, increment, interval=month, informat=monyy5, format=monyy5); 33 %sysfunc(intnx(&interval,%sysfunc(inputn(&start,&informat)),&increment),&format) 34 %mend intnx; 35 36 %put %intnx(start=SEP14,increment=-3); JUN14
which allows you to do stuff like:
37 38 %let Last_3month=%intnx(&latest_date,-2)--%intnx(&latest_date,0); 39 %let Prev_3month=%intnx(&latest_date,-5)--%intnx(&latest_date,-3); 40 %let Last_6month=%intnx(&latest_date,-5)--%intnx(&latest_date,0); 41 %let Prev_6month=%intnx(&latest_date,-11)--%intnx(&latest_date,-5); 42 %let Last_12month=%intnx(&latest_date,-11)--%intnx(&latest_date,0); 43 %let Prev_12month=%intnx(&latest_date,-23)--%intnx(&latest_date,-11);
Hi,
I'm a bit confused. This is all about how to create a set of macro variables, right? Is below a fair summary?
Given a macro variable &latest_date in the format MONYY (e.g. SEP14), I would like a way to make 6 macro variables:
Last_3month JUL14-SEP14
Prev_3month APR14-JUN14
Last_6month APR14-SEP14
Prev_6month OCT14-MAR14
Last_12month OCT13-SEP14
Prev_12month OCT12-SEP13
?
If so, I would look into the INTNX function. It should make this much easier than trying to break the string up into pieces etc. SAS is very good at working with dates.
Hi Quentin,
Yes, this is exactly what I wanted. Such a good summary. My previous though was also to use INTNX() function. However, I was not sure if I could use it as I have never used 'MONYY'd date constant before. I will try to use it now. Thank you.
You can use the ANYDTDTE. informat to convert &LATEST_DATE to an actual date.
You can use a DO loop to handle your different time windows and INTNX() with PUT() to generate the MONYY strings you want.
You can use CAT() functions to generate both the macro variable name and the values.
%let latest_date=SEP14 ;
data _null_;
last = input(symget('latest_date'),anydtdte.);
format last date9.;
do period=3,6,12 ;
call symputx(cats('prev_',period,'month')
, catx('-',put(intnx('month',last,-2*period+1,'b'),monyy5.)
,put(intnx('month',last,-period,'b'),monyy5.)
)
);
call symputx(cats('last_',period,'month')
, catx('-',put(intnx('month',last,-1*period+1,'b'),monyy5.)
,put(intnx('month',last,0,'b'),monyy5.)
)
);
end;
run;
How wide is your dataset? That sounds like a pain to maintain. I'd highly recommend flipping your dataset and then processing it.
Regardless, I agree with on creating macro variables via intnx and/or call symput as well.
Here's a quick example of obtaining the last 3 months.
%let current_date=%sysfunc(today(), monyy5.);
%put ¤t_date.;
%let prev_3months=%sysfunc(intnx(month, %sysfunc(today()), -3), monyy5.);
%put &prev_3months;
Hi Reeza,
There are only these MONYY variables (12 variables) with about 5 million rows in each dataset. What do you mean by flipping my data? You mean using proc transpose?
I will try to use the INTNX() function as you two suggested.
Thanks,
Hi Reeza (and others),
When I tried to set the current date to SEP14 using code below, the pre_3months didn't resolve. Any suggestions?
%let latest_date='SEP14'd;
%put &latest_date;
%let prev_3months=intnx('month', &latest_date, -3);
%put &prev_3months;
That is because SEP14 does not represent a specific date. 01SEP2014 would represent a date.
Additionally below code won't work as well.
%let prev_3months=intnx('month', &latest_date, -3);
You need to use %sysfunc() when calling a SAS function out of the macro environment.
I suggest you post some sample data (a data step creating such data) and then tell us what you want to achieve - eventually also posting some desired output.
Thanks everyone for your participation and useful suggestions. I learnt quite a bit and got my question resolved.
Hi,
For the goal of creating the macro variables, INTNX works, you just need to use INPUTN to convert your MONYY date into a SAS date, and wrap those in %SYSFUNC if you want to stay in macro land:
27 %let latest_date=SEP14; 28 29 %let Last_3month=%sysfunc(intnx(month,%sysfunc(inputn(SEP14,monyy5.)),-3),monyy5.); 30 %put &Last_3month; JUN14
You could also make your own little macro function, e.g.:
32 %macro intnx(start, increment, interval=month, informat=monyy5, format=monyy5); 33 %sysfunc(intnx(&interval,%sysfunc(inputn(&start,&informat)),&increment),&format) 34 %mend intnx; 35 36 %put %intnx(start=SEP14,increment=-3); JUN14
which allows you to do stuff like:
37 38 %let Last_3month=%intnx(&latest_date,-2)--%intnx(&latest_date,0); 39 %let Prev_3month=%intnx(&latest_date,-5)--%intnx(&latest_date,-3); 40 %let Last_6month=%intnx(&latest_date,-5)--%intnx(&latest_date,0); 41 %let Prev_6month=%intnx(&latest_date,-11)--%intnx(&latest_date,-5); 42 %let Last_12month=%intnx(&latest_date,-11)--%intnx(&latest_date,0); 43 %let Prev_12month=%intnx(&latest_date,-23)--%intnx(&latest_date,-11);
Hi Quentin,
Thanks a lot for your useful suggestions and help. I will mark this as the correct answer with the following minor changes for Prev_6month and Prev_12month.
%let Prev_6month=%intnx(&latest_date,-11)--%intnx(&latest_date,-6);;
%let Prev_12month=%intnx(&latest_date,-23)--%intnx(&latest_date,-12);
You say " I have numeric variables JAN14--DEC14," - what's numeric about these values?
I suggest that instead of trying to deal with strings representing a date you investigate/skill-up how SAS date values and SAS formats work. As soon as you're using SAS date values things will become quite simple as there are a few powerful SAS calendar functions available which can do a lot of the work for you.
Below sample code might give you an idea of how this could work.
data have;
format date monyy.;
do date="01jan2013"d to "31jan2015"d by 10;
var+1;
output;
end;
stop;
run;
proc sql;
create table want1 as
select *
from have
where "01apr2014"d = intnx("quarter",date,0,"b")
;
create table want2 as
select intnx("quarter",date,0,"b") as Start_of_Quarter format=date9.,
avg(var) as avg_var
from have
group by calculated Start_of_Quarter
;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: