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

[DarthPathos]

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

[Reeza · Accepted solution]

%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). 

[Astounding]

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.

[DarthPathos]

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

[DarthPathos]

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)