0

I am looking to save a dataset to an excel and have it save the filename with the current date.

To narrow down the issue, I am looking for code that saves the excel to a path made by combining these 3 things together: '/sas/myPath/results', '3-22-2018', and '.xlsx'.

I can't just say: 

%let filePath = '/sas/myPath/results3-22-2018.xlsx';

because the date will be updated dynamically. The error is the libname referring to a cats function. Any ideas on how to fix this?

/*Set File Path*/
data _null_;
%let filePath = cats('/sas/myPath/results', '3-22-2018', '.xlsx');
run;

/*Output to excel*/
libname excelOut XLSX &filePath;
data excelOut.Sheet1;
set results; 
run;
libname excelOut clear;
Matt
  • 60
  • 1
  • 8
  • Possible duplicate of [Concatenate quoted macro variables](https://stackoverflow.com/questions/25534692/concatenate-quoted-macro-variables) – Reeza Mar 22 '18 at 19:04

1 Answers1

1

There is no need to use any functions to concatenate text in macro code. Everything in macro code is text. Just expand the macro variable where you want to use its value in the code you generate. 

%let datestr=3-22-2018;
%let filePath = "/sas/myPath/results&datestr..xlsx";

Make sure to use double quotes instead of single quotes if you are building quoted strings. The macro processor does not expand macro triggers inside of single quotes. Note that macro processor uses a period following a macro variable name to indicate the end of the name. So to have an actual period appear you need two of them. One for the macro processor to use and one that will become part of the generated value.

Also remember that macro processor does its work and passes the generated code off to SAS. So your first data step will really execute in this order.

%let filePath = cats('/sas/myPath/results', '3-22-2018', '.xlsx');
data _null_;
run;

Notice that your data step is doing literally nothing and is not needed.

Tom
  • 47,574
  • 2
  • 16
  • 29