DATA Step, Macro, Functions and more

How to put monthname as string in output file name

Reply
Frequent Contributor
Posts: 124

How to put monthname as string in output file name

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 ;

*/

Trusted Advisor
Posts: 1,913

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

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.

Super Contributor
Posts: 578

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

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

Ask a Question
Discussion stats
  • 2 replies
  • 1191 views
  • 0 likes
  • 3 in conversation