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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.