BookmarkSubscribeRSS Feed
vminc
Calcite | Level 5

hello

i want to create a macro that takes current date and number of loopback months as input and create a series of macro variables capturing last day of each month

example:

curr_dt: 20141224

number of look back months (n) =6

The output should be (6 statements to create 6 variable names)

%let date_1=sysfunc(intnx('month',-1,inputn(curr_dt,mmddyy6.),e),yymmddn8.);

%let date_2=sysfunc(intnx('month',-2,inputn(curr_dt,mmddyy6.),e),yymmddn8.)

.

.

.

%let date_6=sysfunc(intnx('month',-6,inputn(curr_dt,mmddyy6.),e),yymmddn8.)

Thanks

vminc

6 REPLIES 6
Reeza
Super User

Use a data _null_ step with call symput instead:

For example: 

%let curr_dt="24Dec2014"d;

%let nmonths=6;

data test; *creating a data set for debugging purposes but can be changed to _null_ so no dataset is created;

do i=1 to &nmonths;

var_name=catx("_", date, i);

var_value=put(intnx('month', &curr_dt, i, e), yymmddn8.);

call symput(var_name, var_value);

output;

end;

run;

FriedEgg
SAS Employee

%macro lookback(

curr_dt=%sysfunc(today(), yymmddn8.),

n=

);

%local i inpdte;

%let inpdte=%sysfunc(inputn(&curr_dt.,yymmdd8.));

%do i=1 %to &n.;

   %global date_&i.;

   %let date_&i.=%sysfunc(intnx(month, &inpdte., %eval(&i.*-1), e),yymmddn8.);

   %put &&date_&i..;

%end;

%mend;

%lookback(n=6)

vminc
Calcite | Level 5

Hi Reeza

I want to get the %let statement as I will be using these later.

When I use a do loop to get the %let statements, it does not recognize the ampersand in the variable name and in the sysfunc statement

I was wondering if you can think of any other technique that will produce the % let statements

Thank you

vminc

Patrick
Opal | Level 21

May be you should give us the bigger picture especially why you want these %let statements generated explicitly so that we can propose an appropriate solution.

If it's really about dynamically generating code then one way to go is a "data _null_" step with put statements where you generate the code and write it to an external file. You then can %include this file so that the generated code inside gets executed.

Reeza
Super User

Yeah, getting statements generated like that is a pain in the a**, which is why I don't do it Smiley Happy

Tom
Super User Tom
Super User

Not sure why you need so many macro variables. Personally I do not like the idea of macro "arrays" as I find that most programs are easier to maintain if you use a different coding pattern.

The problem with using a macro to generate macro variables that you want to use later is that by default the macro variables are defined in the scope of the sub-macro and so disappear when it ends.  One way to deal with this is to force the macros to be made in the GLOBAL macro scope as others have posted above. This can cause trouble with other parts of your code that might want to use those same macro variable names for some other meaning.  Here are two methods to allow you define the new macros in the current macro scope (not the scope of the sub macro and not necessarily the GLOBAL scope either).

1) Is to take advantage of a feature of the fact that if a sub macro does not have any macro variables at all then it will not generate a new macro scope.  Note that this means that the macro cannot take any parameters either.  So your number of months will have to placed into another macro variable first before calling the new macro.

%macro lookback;

data _null_;

  do i=1 to input(symget('months'),comma32.);

     call symputx(cats('date_',i),putn(intnx('month',today(),-1*i,'e'),'yymmddn8.'));

  end;

run;

%mend lookback ;

%let months=6;

%lookback;

2) Another method is to have the macro return the text of the %LET statements (without actually running) them as quoted macro text. And then use %UNQUOTE() to execute the %LET statements in the calling environment.  This will let you define the macro with parameters and local macro variables.

%macro lookback(months,start=);

%local i str;

%if ^%length(&start) %then %let start=%sysfunc(today());

%do i=1 %to &months ;

  %let str=&str.%nrstr(%let )date_&i=%sysfunc(intnx(month,&start,-&i,e),yymmddn8.)%nrstr(;);

%end;

&str.

%mend;

%unquote(%lookback(6));

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1244 views
  • 0 likes
  • 5 in conversation