I have an excel file report name Reference_Report. The file name ends with a time stamp every time it is exported to my machine.
My library reference the report in my desktop. Instead of updating the file name, I thought I would use a wild card. When I run the code SAS gives me ERROR: Physical file does not exist, C:\WINDOWS\system32\FILENAME.xlsx
What am I doing wrong?
libname Ref 'C:\Users\1\Desktop\';
%let location = 'C:\Users\1\Desktop\';
%let file = 'Reference_Report';
%let extension ='.xlsx';
%let filename = &location&file*&extension;
proc import
datafile = filename
out = Ref.Report1
dbms = xlsx
replace;
run;
First thing you did not reference your "filename" macro variable.Should look like:
proc import
datafile = &filename.
out = Ref.Report1
dbms = xlsx
replace;
run;
Second your "filename" will be built incorrectly. By placing quote characters in the Location,File and Extension variables your Filename is: 'C:\Users\1\Desktop\''Reference_Report'*'.xlsx'
Which has two single quotes in the middle and other extra quotes. If you want to generate 'C:\Users\1\Desktop\Reference_Report*.xlsx'
You need different code.
%let location = C:\Users\1\Desktop\; %let file = Reference_Report; %let extension =.xlsx; %let filename = "&location.&file.*&extension."; %put &filename.;
Double quotes are used so the macro variables will resolve inside them.
But if you have more than one of the "reference_report.xlsx" files in that folder proc import will choke on that as it doesn't deal with multiple input files.
Hello
Please remove the single quotes from macro definitions. Have them as shown below.
Macro variable includes everything from the equal to sign and semi colon.
%let location = C:\Users\1\Desktop\;
%let file = Reference_Report;
%let extension =.xlsx;
%let filename = &location&file&extension;
%put &=filename;
First thing you did not reference your "filename" macro variable.Should look like:
proc import
datafile = &filename.
out = Ref.Report1
dbms = xlsx
replace;
run;
Second your "filename" will be built incorrectly. By placing quote characters in the Location,File and Extension variables your Filename is: 'C:\Users\1\Desktop\''Reference_Report'*'.xlsx'
Which has two single quotes in the middle and other extra quotes. If you want to generate 'C:\Users\1\Desktop\Reference_Report*.xlsx'
You need different code.
%let location = C:\Users\1\Desktop\; %let file = Reference_Report; %let extension =.xlsx; %let filename = "&location.&file.*&extension."; %put &filename.;
Double quotes are used so the macro variables will resolve inside them.
But if you have more than one of the "reference_report.xlsx" files in that folder proc import will choke on that as it doesn't deal with multiple input files.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: