Macro variable name!

[Dil]

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;

[Quentin]

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.

[Dil]

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.

[Tom]

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;

[Reeza]

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 &current_date.;

%let prev_3months=%sysfunc(intnx(month, %sysfunc(today()), -3), monyy5.);

%put &prev_3months;

[Dil]

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,

[Dil]

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;

[Patrick]

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.

[Dil]

Thanks everyone for your participation and useful suggestions. I learnt quite a bit and got my question resolved.

[Quentin · Accepted solution]

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

[Dil]

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

[Patrick]

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;