topic Macro variables in Macro variables in SAS Programming

Macro variables in Macro variables

Stan76 — Tue, 25 Jul 2017 16:06:24 GMT

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?

Re: Macro variables in Macro variables

Reeza — Tue, 25 Jul 2017 16:11:03 GMT

%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?

Re: Macro variables in Macro variables

Jagadishkatam — Tue, 25 Jul 2017 16:12:43 GMT

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

Re: Macro variables in Macro variables

ballardw — Tue, 25 Jul 2017 19:54:49 GMT

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.

Re: Macro variables in Macro variables

Stan76 — Wed, 26 Jul 2017 07:23:07 GMT

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)

Re: Macro variables in Macro variables

Stan76 — Wed, 26 Jul 2017 07:26:03 GMT

Solved with the call symput - thank you everyone!

Re: Macro variables in Macro variables

Ron_MacroMaven — Wed, 26 Jul 2017 12:11:50 GMT

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