topic Re: "Physical file does not exist" in SAS Programming

"Physical file does not exist"

vargo032 — Mon, 15 Aug 2016 19:35:41 GMT

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;

Re: "Physical file does not exist"

Reeza — Mon, 15 Aug 2016 19:40:00 GMT

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.

Re: "Physical file does not exist"

vargo032 — Mon, 15 Aug 2016 19:59:26 GMT

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.

Re: "Physical file does not exist"

Reeza — Mon, 15 Aug 2016 20:02:14 GMT

Are you closing ODS listing while running this code?

Re: "Physical file does not exist"

vargo032 — Mon, 15 Aug 2016 20:14:35 GMT

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;

Re: "Physical file does not exist"

Reeza — Mon, 15 Aug 2016 20:17:43 GMT

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;

Re: "Physical file does not exist"

vargo032 — Mon, 15 Aug 2016 21:05:46 GMT

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!

Re: "Physical file does not exist"

Reeza — Mon, 15 Aug 2016 21:36:00 GMT

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.

Re: "Physical file does not exist"

vargo032 — Mon, 15 Aug 2016 21:39:25 GMT

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!

Re: "Physical file does not exist"

RW9 — Tue, 16 Aug 2016 09:00:04 GMT

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;

Re: "Physical file does not exist"

cgates — Tue, 01 Sep 2020 00:58:52 GMT

Did you every find a solution? I'm having the exact same issue.

Re: "Physical file does not exist"

SASKiwi — Tue, 01 Sep 2020 01:13:06 GMT

@cgates - Adding to old posts is discouraged on the SAS Community. You will get a better response if you create a new post (Please post your SAS log). You can always add a link to an old post if you think it would be helpful. While you may be getting the same issue there is no guarantee the cause and fix is the same.

Re: "Physical file does not exist"

Kurt_Bremser — Tue, 01 Sep 2020 04:48:58 GMT

Please post your question in a new thread. Post your log (using the proper subwindows as mentioned in the welcome mail), give information about your SAS environment.

Re: "Physical file does not exist"

cgates — Mon, 07 Sep 2020 16:23:09 GMT

https://communities.sas.com/t5/SAS-Programming/Physical-file-does-not-exist-Can-t-Output-File-to-SharePoint/m-p/682033

Re: "Physical file does not exist"

cgates — Mon, 07 Sep 2020 16:23:42 GMT

New post: https://communities.sas.com/t5/SAS-Programming/Physical-file-does-not-exist-Can-t-Output-File-to-SharePoint/m-p/682033