DATA Step, Macro, Functions and more

How to read xlsx file only if it exists

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

How to read xlsx file only if it exists

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?


Accepted Solutions
Solution
‎02-17-2012 02:56 PM
Valued Guide
Posts: 634

How to read xlsx file only if it exists

Posted in reply to WesBarris

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;

View solution in original post


All Replies
Solution
‎02-17-2012 02:56 PM
Valued Guide
Posts: 634

How to read xlsx file only if it exists

Posted in reply to WesBarris

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;

Contributor
Posts: 44

How to read xlsx file only if it exists

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;

Valued Guide
Posts: 634

How to read xlsx file only if it exists

Posted in reply to WesBarris

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

Contributor
Posts: 44

How to read xlsx file only if it exists

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.

Valued Guide
Posts: 634

How to read xlsx file only if it exists

Posted in reply to WesBarris

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

Super User
Super User
Posts: 7,074

How to read xlsx file only if it exists

Posted in reply to WesBarris

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 ;

Contributor
Posts: 44

How to read xlsx file only if it exists

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1449 views
  • 0 likes
  • 3 in conversation