topic Re: Creating a dynamic macro variable for Month and Year to add to a filename in SAS Programming

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

DarthPathos — Tue, 07 Jun 2016 17:51:14 GMT

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

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

Reeza — Tue, 07 Jun 2016 18:00:25 GMT

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

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

Astounding — Tue, 07 Jun 2016 18:13:04 GMT

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.

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

DarthPathos — Tue, 07 Jun 2016 18:22:36 GMT

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)

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

DarthPathos — Tue, 07 Jun 2016 18:24:33 GMT

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