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 ;
*/
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.
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.