I'm trying to write some SAS code that will read in a .xlsx file if it exists, otherwise I would like it to create an empty dataset. I've written some code but it hangs at the "call execute" line. Here is what I am trying:
data _null_;
if fileexist("c:\test.xlsx") then
call execute('proc import datafile="c:\test.xlsx" out=work.lixi dbms=excelcs replace;');
else
call execute('data lixi;infile datalines;input wingband;datalines;;');
run;
What am I doing wrong? Is there a better way to do this?
Add a RUN; statement to each of your CALL EXECUTE steps.
The second CALL EXECUTE could be simplified.
data _null_;
if fileexist("c:\test.xlsx") then
call execute('proc import datafile="c:\test.xlsx" out=work.lixi dbms=excelcs replace;run;');
else
call execute('data lixi;stop;wingband=.;run;');
run;
Add a RUN; statement to each of your CALL EXECUTE steps.
The second CALL EXECUTE could be simplified.
data _null_;
if fileexist("c:\test.xlsx") then
call execute('proc import datafile="c:\test.xlsx" out=work.lixi dbms=excelcs replace;run;');
else
call execute('data lixi;stop;wingband=.;run;');
run;
Thanks Art. However, it SAS still hangs at the first call execute statement. Here is what I have:
data _null_;
if fileexist("c:\test.xlsx") then
call execute('proc import datafile="c:\test.xlsx" out=work.lixi dbms=excelcs replace;run;');
else
call execute('data lixi;stop;wingband=.;run;');
run;
Try changing the DBMS to DMBS=EXCEL rather than EXCELCS.
Hi Art,
When I change DBMS=EXCELCS to DBMS=EXCEL, I get this error in the log:
ERROR: DBMS type EXCEL not valid for import.
When I was trying to get this to work by trial-and-error I stumbled across
DBMS=EXCELCS. It was the only DBMS that worked for me.
I have not used EXCELCS, but I did find this SAS note. Not the same but it might be related. Under some versions of SAS .XLSX can be a problem while .XLS is not.
Just run the import without the rest. Does it still hang?
If not then try writing the code to a file instead.
filename code temp;
data _null_;
file code;
if fileexist("c:\test.xlsx") then
put 'proc import datafile="c:\test.xlsx" out=work.lixi dbms=excelcs replace;run;' ;
else
put 'data lixi;stop;wingband=.;run;';
run;
%inc code / source2 ;
Yes, it still hung. I ended up copying that file and pasting as a new file and the SAS code worked.
Thanks for your help.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.