DATA Step, Macro, Functions and more

Creating a dynamic macro variable for Month and Year to add to a filename

Accepted Solution Solved
Reply
Super Contributor
Posts: 250
Accepted Solution

Creating a dynamic macro variable for Month and Year to add to a filename

Hi all,

 

I'm sure this is a relatively easy question (or there is an alternative way of doing it that's less confusing) but I'm working on automating a number of reports I need to run every month.  For the first report, all I need to do is export the data from a SQL Server view to a tab delimited file.  All that is easy; what I'm stuck on is naming of the file.  I'm using SAS 9.4 locally installed on a Windows 7 machine.

Right now, I have

 

%let mnth = 5;
%let yr = 2016;

proc sql;
create table work.test as
/* a bunch of code */
quit;

proc export data = work.test
      outfile = "C:\Report__&yr&mnth..txt"
      DBMS = tab replace;
      putnames = no;
run;

This all works perfectly, but what I'm trying to do set it up so the &mnth and &yr variables are always the previous month (and year) so I don't have to change them every month.  I've tried  &month = intnx('month',have.date,-1) but then my file name has the intnx function in the file name, which doesn't help :-)

 

 

I've spent some time on Lexjansen.com but can't seem to find anything; maybe I'm looking under the wrong keywords (dynamic, fiscal year, export etc.).

 

Any suggestions?

Thanks!
Chris

Has my article or post helped? Please mark as Solution or Like the article!

Accepted Solutions
Solution
‎06-07-2016 02:24 PM
Super User
Posts: 19,878

Re: Creating a dynamic macro variable for Month and Year to add to a filename

[ Edited ]
Posted in reply to DarthPathos
%let year_month = %sysfunc(intnx(month, %sysfunc(today()), -1, b), yymmn6.);

Edit: Original answer had two typo's (syfunc vs sysfunc and the b does not need quotation marks). 

View solution in original post


All Replies
Solution
‎06-07-2016 02:24 PM
Super User
Posts: 19,878

Re: Creating a dynamic macro variable for Month and Year to add to a filename

[ Edited ]
Posted in reply to DarthPathos
%let year_month = %sysfunc(intnx(month, %sysfunc(today()), -1, b), yymmn6.);

Edit: Original answer had two typo's (syfunc vs sysfunc and the b does not need quotation marks). 

Super User
Posts: 5,518

Re: Creating a dynamic macro variable for Month and Year to add to a filename

[ Edited ]

It's the right idea, but I would suggest you consider:

 

%let year_month=;

%if %length(&year_month)=0 %then %let year_month=  /* same thing */;

 

That way, if you ever want to backdate the report, you only need to change the first %LET statement, not the second.  But under normal circumstances, just let it run.

Super Contributor
Posts: 250

Re: Creating a dynamic macro variable for Month and Year to add to a filename

Posted in reply to Astounding
Ooh that's a cool trick - and one I will definitely need for another project. For this report however I'd never need to backdate - I need to report to the Provincial Government by a set date, and if I miss the deadline, we're screwed O_o. Having said that, thanks for the reply!!
Chris
Has my article or post helped? Please mark as Solution or Like the article!
Super Contributor
Posts: 250

Re: Creating a dynamic macro variable for Month and Year to add to a filename

Here I was thinking you were just testing me - I got both but it took some research :-)

Thanks so much! Works perfectly (now that I understand what it's doing LOL)
Has my article or post helped? Please mark as Solution or Like the article!
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 470 views
  • 2 likes
  • 3 in conversation