BookmarkSubscribeRSS Feed
vargo032
Calcite | Level 5

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;

 

 

14 REPLIES 14
Reeza
Super User

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.

vargo032
Calcite | Level 5

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.

Reeza
Super User

Are you closing ODS listing while running this code?

vargo032
Calcite | Level 5

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;
Reeza
Super User

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;

vargo032
Calcite | Level 5
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!
Reeza
Super User

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. 

vargo032
Calcite | Level 5

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!

cgates
Obsidian | Level 7
Did you every find a solution? I'm having the exact same issue.
SASKiwi
PROC Star

@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.

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 3375 views
  • 0 likes
  • 6 in conversation