How to read xlsx file only if it exists

[WesBarris]

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?

[ArtC · Accepted solution]

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;

[WesBarris]

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;

[ArtC]

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

[WesBarris]

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.

[ArtC]

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

[Tom]

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 ;

[WesBarris]

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.