BookmarkSubscribeRSS Feed
gsnidow
Obsidian | Level 7

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 ;

*/

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
DBailey
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2662 views
  • 0 likes
  • 3 in conversation