DATA Step, Macro, Functions and more

"Physical file does not exist"

Reply
Occasional Contributor
Posts: 5

"Physical file does not exist"

I am running a macro to export using proc print an excel file to a sharepoint site that's mapped to my Y: drive. When I run the program I get no errors, but when it's run in windows batch mode (i.e. command line) I get "

ERROR: Physical file does not exist, Z:\Data_local\my_data\Y:\data\blah.xlsx

 

I'm not sure why SAS adds the Z drive reference to my filename IN THIS CODE:

 

ODS EXCEL FILE = 'Y:\data\blah.xlsx';

proc print noobs data = mydata (where = (id=&id);

run;

ODS EXCEL FILE CLOSE;

 

 

Super User
Posts: 19,791

Re: "Physical file does not exist"

I don't know that SAS would change the directory...make sure you're running the correct code. Add a PUT statement into your code and then call from batch to see if it appears.

Occasional Contributor
Posts: 5

Re: "Physical file does not exist"

Okay, added MLOGIC and MPRINT as well as PUT and it's not resolving the file but this time it's not showing the Z shared drive...weird:

 

ERROR: Physical file does not exist, 'Y:\data\blah.xlsx'

 

There is a previous warning:

WARNING: Data too long for column "DDMM"; truncated to 97 characters to fit.

 

but I know DDMM is only 4 characters because that's the lenght of the data, and i'm using strip().

 

Another co-worker suggested using:

 

style(header)={width=100%}  style(data)={width=100%} ;

 

 

to stop data truncation and clear the warning, but seems to still be an issue, and maybe the root cause of the 'file not exist' issue....?

Also tried clearing libname, filename and using a macro variable instead of the Y: drive to connect. All three do not resolve the issue.

Super User
Posts: 19,791

Re: "Physical file does not exist"

Are you closing ODS listing while running this code?

Occasional Contributor
Posts: 5

Re: "Physical file does not exist"

Yes, later on in the program for an email macro but that's seperate of the export

 

Here's a condensed version of the code:

 

%put ***&records***;

options mprint mlogic;
%macro export_macro;

%if &records ne "" %then %do;
%let i=1;
%do %while ( &i<=&records);

data _null_;
set list_id_ (where =(list_id=&i));
call symput ('file_id',strip(file_id)) ;
run;

%put ***&file_id*** ;
ods excel file="Y:\data\blah_&file_id..xlsx" 
		  title = "&file_id._1" 
;
proc print noobs data=current_reports (where = (file_id="&file_id" style(header)={width=100%} style(data)={width=100%};
run;
ods excel close;

%end;
%let i= %eval(&i+1);
%end; %end;
%mend;
%export_macro;
Super User
Posts: 19,791

Re: "Physical file does not exist"

I don't see ODS LISTING CLOSE in that code. 

 

Try adding that before your proc print and turning it on afterwards. I think that's generating the error/warning.

 

ODS LISTING CLOSE;

 

ODS LISTING;

Occasional Contributor
Posts: 5

Re: "Physical file does not exist"

Thank you! That got rid of the data truncation but still having issues with the file output, only in batch mode.
Same error as before.
I added a loop to try running a second time:
If &syserr > 0
but that returns false, which makes no sense!
Super User
Posts: 19,791

Re: "Physical file does not exist"

When you run in batch, what user ID is being used? 

I don't run in batch so not sure, but this is a common issue I've heard. 

 

The account being used needs to have same permissions and set up as the account you're using for tests. 

Occasional Contributor
Posts: 5

Re: "Physical file does not exist"

it would make sense if there's a difference between my SAS config file and the PC that runs the batch. Using windows authentication if that helps. I'll take a look at the system info to find any differences.

 

Thank you for the help!

Super User
Super User
Posts: 7,955

Re: "Physical file does not exist"

Hi,

 

Just a small point, your code can be simply resolved to:

data _null_;
  set list_id;
  call execute(cats('ods excel file="y:\data\blah_',file_id,'.xlsx" title="',file_id,'";
                     proc print noosb data=current_reports (where=(file_id="',file_id,'"; run;
                     ods excel close;'));
run;
Ask a Question
Discussion stats
  • 9 replies
  • 607 views
  • 0 likes
  • 3 in conversation