When I pass a file value as file path and file in a macro, why I continue to hit errors with an extra slash '/' in front of the file name:
%LET fn="/dir/folder/filename.xls";
%ImportDataSheet(df=&fn.);
The Macro code
"proc import datafile=&df dbms=xls out=resulename replace;"
got a "Can not load sas log" error, with the below file path/name showing:
/dir/folder//filename.xls
The program inserted an extra '/' in front of the file name, preventing successful loading of the file.
How do I successfully pass file path/name variables?
Appreciate your help on this very much!
@zz wrote:
Requested Sheet Name not found on Excel -> /dir/folder/data//datafile.xls
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
Check your excel file and ensure it has a sheet labeled DT without any extra spaces.
I would also suggest just running that code, without the macro portion until you get it working and then switch to the macro code.
Although that may appear to be the error, it's not. There's something else inyour code.
Please post your full code and log, using the code icon above, rather than plain text in the window.
The full code/log should provide enough info to help find the error.
Thank you, Reeza! Here is the code and log:
%MACRO ImportDataSheet( df= );
proc import datafile=&df dbms=xls out=subjects_nmi replace;
sheet='dt';
getnames=yes;
run;
%MEND ImportDataSheet;
%let dpf = "/dir/folder/data/datafile.xls";
%PUT dpf=&dpf.;
%ImportDataSheet( df = &dpf. );
********************
9 %MACRO ImportDataSheet( df= );
10 proc import datafile=&df dbms=xls out=subjects_nmi replace;
11 sheet='dt';
12 getnames=yes;
13 run;
14 %MEND ImportDataSheet;
15
16 %let dpf = "/dir/folder/data/datafile.xls";
17
18 %PUT dpf=&dpf.;
dpf="/dir/folder/data/datafile.xls"
19
20 %ImportDataSheet( df = &dpf. );
Requested Sheet Name not found on Excel -> /dir/folder/data//datafile.xls
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
@zz wrote:
Requested Sheet Name not found on Excel -> /dir/folder/data//datafile.xls
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
Check your excel file and ensure it has a sheet labeled DT without any extra spaces.
I would also suggest just running that code, without the macro portion until you get it working and then switch to the macro code.
Also the path you are passing is relative and will expecte the file mentioned to be in a folder subordinate to the current active path SAS is using. Which may very well not be where you expect. I would recommend always using a complete path to insure the location.
Also, are you running in a server environment? The input file would have to be on the server to be found.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.