How to put monthname as string in output file name

gsnidow — Tue, 10 Sep 2013 12:16:11 GMT

Greetings all. I am developing a monthly report that needs to be named with the run date, report name, report month and year. For example, if today is 2013-09-10, and I am running the report for August 2013, the output Excel file must be named '2013-09-10 report name Augst 2013.xlsx'. The base data for the report comes from an ODBC query, and I have a macro variable for the begin date, 'dtbegin', and the end date is caclulated. My issue is in create the output file name. I am basically formating the date variables as yymmdd10. so I can pull out the year, month, and day, then building them back into YYYY-MM-DD format. I need to have the dates in yymmdd10. format for the pass through to DB2.

*the end date will be calculated, and also need to be in yymmdd10. format ;

%Let dtbegin = '2013-08-01' ;

%Let fpath = 'C:\imports\' ;

data null ;

add one month to the begin date ;

dt2 = intnx('month',input( &dtbegin, yymmdd10.),1) ;

get the year, month, and day of dt2, so I can build the ending date in YYYY-MM-DD format ;

there may be an easier way to do this, but this is the only way I could get it to work ;

yr = year(dt2) ;

mo = reverse(substr(cat(trim(reverse(month(dt2))),'0'),1,2)) ;

dy = reverse(substr(cat(trim(reverse(day(dt2))),'0'),1,2)) ;

put the date components into dtend, which is the other parameter of the pass through query ;

call symput('dtend',cat("'",yr,'-',trim(mo),'-',trim(dy),"'")) ;

get the date components of today's date to build the date of the report run for the outfile name ;

yr = year(date()) ;

mo = reverse(substr(cat(trim(reverse(month(date()))),'0'),1,2)) ;

dy = reverse(substr(cat(trim(reverse(day(date()))),'0'),1,2)) ;

put dtbegin into date format so I can get the year and month for the outfile name ;

dt1 = input(&dtbegin, yymmdd10.) ;

yr2 = year(dt1) ;

mo_name = put(dt1, monname.) ;

put all the parts together to build the outfile name ;

call symput('ExcelName',&fpath||cat(yr,'-',trim(mo),'-',trim(dy),' reportname '||trim(mo_name)||' '||trim(yr2)||'.xlsx')) ;

Show the value of &ExcelName. This issue here is the spaces aroung 'August' and '2013' <<<<<<< this will show the issue ;

put "&ExcelName." ;

run ; quit ;

ODBC query with &dtbegin and &dtend in the where clause. This is working ok.

Many more steps here before exporting the Excel file. No issues here.

proc export data=final

outfile="&ExcelName"

dbms=excel replace ;

sheet='Sheet1' ;

run ;

Re: How to put monthname as string in output file name

PaigeMiller — Tue, 10 Sep 2013 12:27:21 GMT

See examples 1 and 2 of the docs for %SYSFUNC. You would probably want to use the MONNAME. format. Or use the MONNAME. format in CALL SYMPUTX.

Re: How to put monthname as string in output file name

DBailey — Tue, 10 Sep 2013 12:32:16 GMT

Does this not get what you need:

%Let dtbegin = '2013-08-01' ;

%Let fpath = 'C:\imports\' ;

data work.test;

dt2=intnx('month',input( &dtbegin, yymmdd10.),1) ;

call symput('fname',cat('',&dtbegin,' report name',put(dt2,monname10.),' ',put(year(dt2),4.),'.xlsx'));

run;

%put fname=&fname;

fname= 2013-08-01 report name September 2013.xlsx

topic How to put monthname as string in output file name in SAS Programming

How to put monthname as string in output file name

Re: How to put monthname as string in output file name

Re: How to put monthname as string in output file name