topic How to read xlsx file only if it exists in SAS Programming

How to read xlsx file only if it exists

WesBarris — Fri, 17 Feb 2012 19:39:22 GMT

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?

How to read xlsx file only if it exists

ArtC — Fri, 17 Feb 2012 19:56:08 GMT

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;

How to read xlsx file only if it exists

WesBarris — Fri, 17 Feb 2012 20:33:34 GMT

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;

How to read xlsx file only if it exists

Tom — Fri, 17 Feb 2012 20:36:22 GMT

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 ;

How to read xlsx file only if it exists

WesBarris — Fri, 17 Feb 2012 21:41:26 GMT

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.

How to read xlsx file only if it exists

ArtC — Fri, 17 Feb 2012 21:55:13 GMT

Try changing the DBMS to DMBS=EXCEL rather than EXCELCS.

How to read xlsx file only if it exists

WesBarris — Mon, 20 Feb 2012 14:52:01 GMT

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.

How to read xlsx file only if it exists

ArtC — Mon, 20 Feb 2012 17:08:05 GMT

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.

http://support.sas.com/kb/37/479.html