BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Stan76
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

6 REPLIES 6
Reeza
Super User

%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
Calcite | Level 5

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
Amethyst | Level 16

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

 

Thanks,
Jag
ballardw
Super User

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
Calcite | Level 5

Solved with the call symput - thank you everyone!

Ron_MacroMaven
Lapis Lazuli | Level 10

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

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
  • 1155 views
  • 0 likes
  • 5 in conversation