DATA Step, Macro, Functions and more

How to troubleshoot Physical file does not exist?

Reply
Frequent Contributor
Posts: 99

How to troubleshoot Physical file does not exist?

[ Edited ]
data cars;set sashelp.cars;run;
%let xlsfile1 = C:\Users\1558977\Desktop\autocreate\cars.xlsx;

proc export outfile="&xlsfile1" data=cars dbms=xlsx replace;
run; 
quit;

   * This next routine starts the Excel application.   *;
filename cmds dde 'excel|system';
x "'C:\Program Files\Microsoft Office\Office11\excel.exe'";
data _null_;
   x=sleep(3);
run;

data _null_;
   file cmds;
   put '[open("'"&xlsfile1"'")]';
run;

The intent is to open the excel file and save it password protected.

But SAS keeps giving this errorSmiley Tonguehysical file does not exis, excel|sytem

 

UPDATE: The error is linked to excel.exe file not found. If I have Excel running already, the code works fine. So need to search for that excel.exe. 

 

data cars;set sashelp.class;run;
%let xlsfile1 = C:\Users\1558977\Desktop\autocreate\cars.xls;
%let xlsfile2 = C:\Users\1558977\Desktop\autocreate\carspassword.xls;

proc export outfile="&xlsfile1" data=cars dbms=xls replace;
run; 
quit;

* This next routine starts the Excel application.   *;
filename cmds dde 'excel|system';

data _null_ ;
	file cmds;
	put '[open("'"&xlsfile1"'")]';
	put '[SAVE()]';
	put '[Save.as("'"&xlsfile2"'",1,"'"test"'")]';
	PUT '[FILE-CLOSE()]';
run;

I've amended by deleting the point towards excel.exe. But for this to work, it requires Excel to be opened first.

 

Contributor
Posts: 43

Re: How to troubleshoot Physical file does not exist?

Could you send log please. I dont sure but
outfile="&xlsfile1" or outfile=&xlsfile1
Super User
Posts: 17,750

Re: How to troubleshoot Physical file does not exist?

Does Excel open? What step is it failing at? 

 

What happens if rather than a macro variable for the open command, you use the full path?

Super User
Posts: 10,466

Re: How to troubleshoot Physical file does not exist?

Post the actual log with the code. Your  example has a couple of different opportunities for a file not to exist.

 

Also are you running in a server environment? If so you may have mulitple other issues such as the paths not being recognized bythe server (causing file not to exist) and that Excel is not available.

 

The DDE Excel SAVEAS command wants a file format indicator (you may be using 1 for that in your syntax) that is variable depending ont the file type created such as XLSX or XLS (different formats) and depends on the version of EXCEL doing the saving. For example 56 is to save as an XLS file from Excel 2007-2010. At least in the last working DDE example I have.

Ask a Question
Discussion stats
  • 3 replies
  • 176 views
  • 0 likes
  • 4 in conversation