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

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

Alok_Pal — Mon, 21 Aug 2017 21:54:04 GMT

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;

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

Reeza — Mon, 21 Aug 2017 21:56:24 GMT

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

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

SASKiwi — Mon, 21 Aug 2017 23:18:08 GMT

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

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

Alok_Pal — Tue, 22 Aug 2017 13:49:53 GMT

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

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

Quentin — Tue, 22 Aug 2017 14:15:36 GMT

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.

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

Alok_Pal — Tue, 22 Aug 2017 14:25:59 GMT

Thanks Quentin. That's an interesting information. I'm gonna try it and see if it works.