BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
adilar39
Obsidian | Level 7

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.

image.jpg

 

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

 

Thanks in advance.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

 

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

Patrick_0-1702853527037.png

 

 

View solution in original post

8 REPLIES 8
JosvanderVelden
SAS Super FREQ
Do you also get the error when you add the line "ODS _ALL_ CLOSE;" before "ods excel file..."?
adilar39
Obsidian | Level 7
Yes, still the same error.
andreas_lds
Jade | Level 19

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

adilar39
Obsidian | Level 7
Yes, it's still showing the same error.
Kurt_Bremser
Super User

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

adilar39
Obsidian | Level 7
But I can upload excel files just fine into SAS Contents. Is that not counted as "Create file" too?
Patrick
Opal | Level 21

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

 

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

Patrick_0-1702853527037.png

 

 

adilar39
Obsidian | Level 7
Nice, that works just like I want. Thank you so much!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 8 replies
  • 867 views
  • 3 likes
  • 5 in conversation