Macro variables in Macro variables

[Stan76]

Hi - I'm trying to automate something in SAS, but I'm very rusty.  I want to create a macro variable that reflects the current months date that I can use to identify a dataset in a proc sql later on.

I've already set up a macro variable which gives me the first of the current month in a date format - I'll call this &currentmonth I want to use this data to identify a dataset that we'll call libname.accounts201707.

So I create a variable like this:

%let currfile = year(&currmonth)||month(currmonth);

which should resolve to 201707, yes?

But when I create the proc sql:

proc sql;

create table example as

select  field1,

           field2

from   libname.accounts&currfile;

quit;

it doesn't work as it's looking for:

libname.accountsyear(&currmonth)||month(currmonth)

rather than

libname.accounts201707

I feel like I'm so close to cracking this but it's getting the better of me...?

Any help?

[Reeza]

%SYSFUNC() -> need this to use functions in macro to differentiate between text and code. There' s a fairly simple solution though, using a format.

%let current_month = %sysfunc(today(), yymmn6.);
%put &current_month;

---

@Stan76 wrote:

Hi - I'm trying to automate something in SAS, but I'm very rusty.  I want to create a macro variable that reflects the current months date that I can use to identify a dataset in a proc sql later on.

I've already set up a macro variable which gives me the first of the current month in a date format - I'll call this &currentmonth I want to use this data to identify a dataset that we'll call libname.accounts201707.

So I create a variable like this:

%let currfile = year(&currmonth)||month(currmonth);

which should resolve to 201707, yes?

But when I create the proc sql:

proc sql;

create table example as

select  field1,

           field2

from   libname.accounts&currfile;

quit;

it doesn't work as it's looking for:

libname.accountsyear(&currmonth)||month(currmonth)

rather than

libname.accounts201707

I feel like I'm so close to cracking this but it's getting the better of me...?

Any help?

---

[Stan76]

I tried this:

%let current_month = %sysfunc(today(), yymmn6.);
%put &current_month;

and it works fine, however when I replace today() with &currmonth. I get the following error:

476 %let current_month = %sysfunc(&currmonth., yymmn6.);

ERROR: Function name missing in %SYSFUNC or %QSYSFUNC macro function reference.

(fyi - &currmonth = '01jun2017'd)

[Jagadishkatam]

Could you please let us know the error or warning you are getting with the one you tried

[ballardw · Accepted solution]

Since you do not show what your value for &currmonth actually is (hint: %put &currmonth; ) then it is hard to say. There are so many ways that people play with dates I'm not going to assume anything about your specific values without an example.

Here is working through an example and shows why you likely have at least 2 errors in your currfile value:

%let year = %sysfunc( year( %sysfunc( today() ) ) );
%put Year is &year;
%let month = %sysfunc( month( %sysfunc( today() ) ) );
%put Month is &month ;  /* NOTE there is no leading 0*/

%let month = %sysfunc(putn (%sysfunc( month( %sysfunc( today() ) ) ),Z2.));
%put Month is &month ; 


%let currfile = &year||&month;
%put Currfile is &currfile;
/* correct concatenation of two macro variables*/
%let currfile = &year.&month;
%put Currfile is &currfile;


/* Or */
data _null_;
   currfile= cats(year(today()),put(month(today()),z2.));
   call symputx ('currfile',currfile);
run;

%put &currfile;

Sometimes it is easer to use data step functions in a data step and create a macro variable than to manipulate in macro language.

[Stan76]

Solved with the call symput - thank you everyone!

[Ron_MacroMaven]

Here's a paper that discusses handling dates in the macro language.

http://www.sascommunity.org/wiki/Macro_Loops_with_Dates

Ron Fehd  date info maven