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: 632

How to read xlsx file only if it exists

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: 632

How to read xlsx file only if it exists

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: 632

How to read xlsx file only if it exists

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: 632

How to read xlsx file only if it exists

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: 6,502

How to read xlsx file only if it exists

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.

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

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