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;
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.
Thanks for your quick response Reeza. Is there's any other way I can do it, may be via DIS job?
Check with your SAS Admin - you can email your attachment on the SAS server if emailing from SAS is set up correctly.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.