topic Re: Physical file does not exist SAS Viya ODS Excel in SAS Programming

Physical file does not exist SAS Viya ODS Excel

adilar39 — Fri, 15 Dec 2023 09:23:13 GMT

Hello everyone, I would like to ask a question. I am using SAS Viya 3.5.

I found this code about exporting dataset as an excel file.

proc sort data=sashelp.class out=class_age;
by age;
run;
proc sort data=sashelp.class out=class_sex;
by sex;
run;

ods excel file='/home/it3/test.xlsx' options(sheet_name='Data' sheet_interval='table');
proc print noobs data=work.class_age;
run;
proc print noobs data=work.class_sex;
run;
ods excel close;

The path file "/home/it3/test.xlsx" is for it3 user only, which mean that the file can only be accessed by that user. I want the result to be saved on public folder, where everyone can access it. So I change the path location into new one like this.

proc sort data=sashelp.class out=class_age;
by age;
run;
proc sort data=sashelp.class out=class_sex;
by sex;
run;

ods excel file='/Public/1. Test/test.xlsx' options(sheet_name='Data' sheet_interval='table');
proc print noobs data=work.class_age;
run;
proc print noobs data=work.class_sex;
run;
ods excel close;

But the error show up, saying that "Physical file does not exist, /Public/1. Test/test.xlsx". Here's the log.

1    %studio_hide_wrapper;
83   %studio_show_only_notes_wrapper;
NOTE: ODS statements in the SAS Studio environment may disable some output features.
86   proc sort data=sashelp.class out=class_age;
87   by age;
88   run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS_AGE has 19 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
89   proc sort data=sashelp.class out=class_sex;
90   by sex;
91   run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS_SEX has 19 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      
92   
93   ods excel file='/Public/1. Test/test.xlsx' options(sheet_name='Data' sheet_interval='table');
94   proc print noobs data=work.class_age;
95   run;
NOTE: There were 19 observations read from the data set WORK.CLASS_AGE.
NOTE: The PROCEDURE PRINT printed page 9.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.16 seconds
      cpu time            0.18 seconds
      
96   proc print noobs data=work.class_sex;
97   run;
NOTE: There were 19 observations read from the data set WORK.CLASS_SEX.
NOTE: The PROCEDURE PRINT printed page 10.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.16 seconds
      cpu time            0.16 seconds
      
98   ods excel close;
ERROR: Physical file does not exist, /Public/1. Test/test.xlsx.
99   
100  
101  %studio_hide_wrapper;
112  
113

Where did I do wrong with the path statement? I have administrator authorization, so I'm pretty sure it's not because I don't have the access to the path.

I've tried inserting URI path too, but it's still showing the same error.

Here's the image of SAS Studio where I want to save the file.

I want the exported file to be saved on the red box.

Thanks in advance.

Re: Physical file does not exist SAS Viya ODS Excel

JosvanderVelden — Fri, 15 Dec 2023 11:23:38 GMT

Do you also get the error when you add the line "ODS _ALL_ CLOSE;" before "ods excel file..."?

Re: Physical file does not exist SAS Viya ODS Excel

andreas_lds — Fri, 15 Dec 2023 11:45:26 GMT

Does the problem persist if you use a path without blanks?

Re: Physical file does not exist SAS Viya ODS Excel

Kurt_Bremser — Fri, 15 Dec 2023 12:36:23 GMT

SAS Content does not point to a physical path, but to SAS metadata. You can't create any file there, only SAS-specific content.

You need to have such a public resource set up by your SAS administrator (who must also be able to act as "root" on the underlying UNIX system).

Re: Physical file does not exist SAS Viya ODS Excel

adilar39 — Fri, 15 Dec 2023 15:12:24 GMT

Yes, still the same error.

Re: Physical file does not exist SAS Viya ODS Excel

adilar39 — Fri, 15 Dec 2023 15:12:59 GMT

Yes, it's still showing the same error.

Re: Physical file does not exist SAS Viya ODS Excel

adilar39 — Fri, 15 Dec 2023 15:17:41 GMT

But I can upload excel files just fine into SAS Contents. Is that not counted as "Create file" too?

Re: Physical file does not exist SAS Viya ODS Excel

Patrick — Sun, 17 Dec 2023 23:53:07 GMT

Thanks to this discussion I could find a solution that works in my Viya4 environment.

I'm still on a steep Viya learning curve so take the following with a grain of salt.

As I understand it what you see under SAS Content is not a direct view of a file system directory structure but what the SAS Content server surfaces to you.

"The FILESRVC access method enables you to store and retrieve user content using the SAS Viya Files service."

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/p0qapul7pyz9hmn0zfoefj0c278a.htm#n1gpta2uobo173n1j6vf9qnkqti7

I can't write to Public in my environment. The following code works for me for SAS Content folder /Users/<my user>

%let myfile=test.xlsx;

filename out filesrvc 
  folderpath="/Users/&sysuserid" 
  filename="&myfile"
  DEBUG=HTTP
  ;

ods excel 
  file=out
  options(
      sheet_name='Data' 
      sheet_interval='table'
    )
  ;
proc print noobs data=sashelp.class;
run;
ods excel close;

Using SAS Studio you can right click onto an object under SAS Content to get the path without the need to type it

Re: Physical file does not exist SAS Viya ODS Excel

adilar39 — Mon, 18 Dec 2023 06:52:01 GMT

Nice, that works just like I want. Thank you so much!