topic Re: ERROR: Physical file does not exist when using a Wildcard in Filename Statement in SAS Programming

ERROR: Physical file does not exist when using a Wildcard in Filename Statement

marathoner — Thu, 13 May 2021 16:35:53 GMT

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;

Re: ERROR: Physical file does not exist when using a Wildcard in Filename Statement

Sajid01 — Thu, 13 May 2021 16:47:12 GMT

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;

Re: ERROR: Physical file does not exist when using a Wildcard in Filename Statement

ballardw — Thu, 13 May 2021 16:51:26 GMT

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.