DATA Step, Macro, Functions and more

Macro variables in Macro variables

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Macro variables in Macro variables

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?


Accepted Solutions
Solution
a month ago
Super User
Posts: 10,538

Re: Macro variables in Macro variables

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.

 

View solution in original post


All Replies
Super User
Posts: 17,912

Re: Macro variables in Macro variables

[ Edited ]

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


 

New Contributor
Posts: 4

Re: Macro variables in Macro variables

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)

Trusted Advisor
Posts: 1,131

Re: Macro variables in Macro variables

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

 

Thanks,
Jag
Solution
a month ago
Super User
Posts: 10,538

Re: Macro variables in Macro variables

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.

 

New Contributor
Posts: 4

Re: Macro variables in Macro variables

Solved with the call symput - thank you everyone!

Regular Contributor
Posts: 200

Re: Macro variables in Macro variables

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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