Desktop productivity for business analysts and programmers

Generate the automated SAS user's report in Excel and send it as an email attachment

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

Generate the automated SAS user's report in Excel and send it as an email attachment

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;

 


Accepted Solutions
Solution
‎08-24-2017 12:16 PM
PROC Star
Posts: 1,471

Re: Generate the automated SAS user's report in Excel and send it as an email attachment

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.

View solution in original post


All Replies
Super User
Posts: 23,997

Re: Generate the automated SAS user's report in Excel and send it as an email attachment

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 Smiley Sad.
Contributor
Posts: 49

Re: Generate the automated SAS user's report in Excel and send it as an email attachment

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

Super User
Posts: 4,019

Re: Generate the automated SAS user's report in Excel and send it as an email attachment

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

Solution
‎08-24-2017 12:16 PM
PROC Star
Posts: 1,471

Re: Generate the automated SAS user's report in Excel and send it as an email attachment

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.

Contributor
Posts: 49

Re: Generate the automated SAS user's report in Excel and send it as an email attachment

Thanks Quentin. That's an interesting information. I'm gonna try it and see if it works.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 370 views
  • 2 likes
  • 4 in conversation