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 ; */
... View more
