BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Alok_Pal
Quartz | Level 8

I have a sas program that generates the user's report to list the various roles and groups associated with each users in SAS environment and generates a report in sas enterprise guide. I want this report to be written in excel on my local machine, send it out as an email attachment and schedule the process to run on Friday of every week.

 

The attached sas program is being used for generating user report. To this code, I tried adding following code (Option 1 & Option 2 below) to get the data in excel on my local machine. But it seems like, either way it is takes the path to my local directory and appends it with that of server's directory path and everytime it fails with following error.

"ERROR: Insufficient authorization to access /opt/sasinside/config/Lev1/SASApp/C:\Temp\UserReport.xls"

I want the excel report to be saved on my local machine "C:\Temp" and send it out as an email attachment.

 

 

Option 1: Using DATA step.

 

PROC EXPORT DATA= WORK.metadata_users_ready
OUTFILE= "C:\Temp\UserReport.xls"
DBMS=csv REPLACE;
SHEET="Results";
RUN;

 

Option 2: Using ODS facility.

 

ods excel file='C:\Temp\UserReport.xls';
proc print data=WORK.metadata_users_ready;
run;
ods excel close;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

You should be able to generate the Excel file on the server (not on your local PC), and email it from the server (assuming emailing is set up on your server).  This should work whether you submit the code as a DI job, EG sesssion, stored process, or whatever way it runs on the server.

 

The key part to remember is your code is running on a server, not your PC.  So you can't point to your C: drive from the code, because your (linux?) server doesn't see your local C: drive.  The error you get is basically SAS saying "I don't know what C:\Temp is."

 

For example, you can try:

 

ods excel file="%sysfunc(pathname(work))/UserReport.xlsx";
proc print data=WORK.metadata_users_ready;
run;
ods excel close;

That will write a .xlsx file to whatever directory is being used as the work dir of your current session.

 


If you're curious where this directory is, you can add:

%put %sysfunc(pathname(work));

That directory will be deleted when your SAS session closes.

 

 

After the Excel file has been written, you should be able to email it as an attachment.  There are lots of posts/papers about emailing from SAS, but as suggested, you may need to check with your admin to see how emailing is set up on your server.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

5 REPLIES 5
Reeza
Super User
If you're running on a server you have to create the file on the server and then move it down to your computer using a copy/move task. It's one of the drawbacks of using a server :(.
Alok_Pal
Quartz | Level 8

Thanks for your quick response Reeza. Is there's any other way I can do it, may be via DIS job?

SASKiwi
PROC Star

Check with your SAS Admin - you can email your attachment on the SAS server if emailing from SAS is set up correctly. 

Quentin
Super User

You should be able to generate the Excel file on the server (not on your local PC), and email it from the server (assuming emailing is set up on your server).  This should work whether you submit the code as a DI job, EG sesssion, stored process, or whatever way it runs on the server.

 

The key part to remember is your code is running on a server, not your PC.  So you can't point to your C: drive from the code, because your (linux?) server doesn't see your local C: drive.  The error you get is basically SAS saying "I don't know what C:\Temp is."

 

For example, you can try:

 

ods excel file="%sysfunc(pathname(work))/UserReport.xlsx";
proc print data=WORK.metadata_users_ready;
run;
ods excel close;

That will write a .xlsx file to whatever directory is being used as the work dir of your current session.

 


If you're curious where this directory is, you can add:

%put %sysfunc(pathname(work));

That directory will be deleted when your SAS session closes.

 

 

After the Excel file has been written, you should be able to email it as an attachment.  There are lots of posts/papers about emailing from SAS, but as suggested, you may need to check with your admin to see how emailing is set up on your server.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Alok_Pal
Quartz | Level 8
Thanks Quentin. That's an interesting information. I'm gonna try it and see if it works.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 2039 views
  • 2 likes
  • 4 in conversation