topic Re: Send Excel Attachment without file path in SAS Programming

Send Excel Attachment without file path

CharlesFowler72 — Thu, 13 Sep 2018 14:09:15 GMT

Hi guys,

so in my work set up when im using sas I cannot accesses files directly from file paths (e.g. C:\documents\Summary.pdf) meaning I cannot open or save anything down.

What I am trying to achieve is to send a data set "D_Jumpers" as an excel attachment in an email.

At the moment the code I have is...

FILENAME mail EMAIL TO='charles.fowler@company.co.uk'

SUBJECT="D Jumpers" CONTENT_TYPE="text/html";

ODS HTML BODY=mail;
PROC PRINT DATA=D_Jumpers;
RUN;
ODS HTML CLOSE;

this puts the table in the body of the email. But I cant find anything online which puts the dataset in an excel as an attachment without referencing a "C:\filepath" anywhere.

any help? something tells me I will be able to do a slight modification on the above code that will work but not sure what it is.

thank you in advanced.

Re: Send Excel Attachment without file path

ballardw — Thu, 13 Sep 2018 14:20:49 GMT

@CharlesFowler72 wrote:

Hi guys,

so in my work set up when im using sas I cannot accesses files directly from file paths (e.g. C:\documents\Summary.pdf) meaning I cannot open or save anything down.

What I am trying to achieve is to send a data set "D_Jumpers" as an excel attachment in an email.

At the moment the code I have is...
FILENAME mail EMAIL TO='charles.fowler@company.co.uk'

SUBJECT="D Jumpers" CONTENT_TYPE="text/html";

ODS HTML BODY=mail;
PROC PRINT DATA=D_Jumpers;
RUN;
ODS HTML CLOSE;
this puts the table in the body of the email. But I cant find anything online which puts the dataset in an excel as an attachment without referencing a "C:\filepath" anywhere.

any help? something tells me I will be able to do a slight modification on the above code that will work but not sure what it is.

thank you in advanced.

Can you describe why you "cannot accesses files directly from file paths"? It might help to show some code and messages from the loq when attempting to do so. Perhaps it is a specific location issue.

Can you create libraries anywhere?

Re: Send Excel Attachment without file path

CharlesFowler72 — Thu, 13 Sep 2018 15:04:58 GMT

essentially its because it is blocked by the company I work for. So for example If I were trying to create a PDF (which ive tried before) using the code

ods pdf file="c:\temp\Alfred.pdf";
 proc print data=D_Jumpers;
 title "D Jumpers";
 run;
 ods pdf close;
 title;

then the error would be "ERROR: Insufficient authorization to access /grasp/sas92/config/sascompute/Lev1/SASApp/c:\temp\Alfred.pdf.". I've spoken to the IT department and have been told it cannot be done.

yeah we have plenty of library space to save the output data set.

Re: Send Excel Attachment without file path

Tom — Thu, 13 Sep 2018 15:40:20 GMT

@CharlesFowler72 wrote:

essentially its because it is blocked by the company I work for. So for example If I were trying to create a PDF (which ive tried before) using the code
ods pdf file="c:\temp\Alfred.pdf";
 proc print data=D_Jumpers;
 title "D Jumpers";
 run;
 ods pdf close;
 title;
then the error would be "ERROR: Insufficient authorization to access /grasp/sas92/config/sascompute/Lev1/SASApp/c:\temp\Alfred.pdf.". I've spoken to the IT department and have been told it cannot be done.

yeah we have plenty of library space to save the output data set.

Your SAS code is running on Unix. You cannot use Windows syntax to reference files when running on Unix. You could create a shared network location that could be access by both the Unix server and your Windows machine(s). But the syntax for the path will most likely look different. /grasp/data/temp/Alfred.pdf vs \\grasp_server\data\temp\Alfred.pdf

Re: Send Excel Attachment without file path

CharlesFowler72 — Thu, 13 Sep 2018 15:44:30 GMT

yes that sounds right. It has been explained to me in the past, a while back now. I work for a pretty big company and trying to change the system is essentially not an option. I need to just try and work round these problems and find alternative ways of doing things.

So this is my problem in trying to send a dataset as an excel attachment. All examples I find online use filepaths which just isn't an option for me.

Re: Send Excel Attachment without file path

Kurt_Bremser — Thu, 13 Sep 2018 16:02:10 GMT

How did you create the Excel in the first place?

If you use filename excel or ods excel, the resulting file will aready be stored on the server for filename email to pick it up.

Re: Send Excel Attachment without file path

CharlesFowler72 — Thu, 13 Sep 2018 16:06:16 GMT

I haven't created it. Its just a temp data step at the moment "D_Jumpers". Am I just missing something really simple?

Re: Send Excel Attachment without file path

Tom — Thu, 13 Sep 2018 16:09:43 GMT

@CharlesFowler72 wrote:

yes that sounds right. It has been explained to me in the past, a while back now. I work for a pretty big company and trying to change the system is essentially not an option. I need to just try and work round these problems and find alternative ways of doing things.

So this is my problem in trying to send a dataset as an excel attachment. All examples I find online use filepaths which just isn't an option for me.

You you just trying to find a path on your Unix box where you can create a file that you want to attach to an email?

Why just just write the file into the WORK directory that SAS is using?

%let path=%sysfunc(pathname(work));

Re: Send Excel Attachment without file path

CharlesFowler72 — Thu, 13 Sep 2018 16:18:42 GMT

that would work. So then what code do I need to use to send the dataset in the work directory as an excel file attachment?

Re: Send Excel Attachment without file path

Tom — Thu, 13 Sep 2018 16:57:45 GMT

It seems pretty clear in the documentation. Or here is an old article about it.

http://www2.sas.com/proceedings/forum2008/038-2008.pdf

So you make an XLSX file some how.

%let path=%sysfunc(pathname(work));

* Make the EXCEL file ;
libname out xlsx "&path/myexcel.xlsx";
data out.report;
   set sashelp.class;
run;

Then when you are sending the email you use the ATTACH= option tell it to attach that file.

FILENAME MailBox EMAIL ATTACH="&path/myexcel.xlsx";

Re: Send Excel Attachment without file path

Kurt_Bremser — Thu, 13 Sep 2018 17:04:54 GMT

@CharlesFowler72 wrote:

I haven't created it. Its just a temp data step at the moment "D_Jumpers". Am I just missing something really simple?

It can't have appeared out of thin air (unless you're Harry Potter, of course). So I have to ask again: how was it created, and how did it end up on your C: drive?

Re: Send Excel Attachment without file path

CharlesFowler72 — Fri, 14 Sep 2018 08:19:34 GMT

At the moment there is no excel file. There is just a dataset in my work library. I need that to be an excel file attached to an email with the problem that I don't have access to a drive to save the excel file down. 🙂 It could be an impossible task.

Re: Send Excel Attachment without file path

CharlesFowler72 — Fri, 14 Sep 2018 08:42:04 GMT

When I run the Libname line I get an error "ERROR: The XLSX engine cannot be found."

Re: Send Excel Attachment without file path

andreas_lds — Fri, 14 Sep 2018 09:59:58 GMT

Which SAS-version are you using? Executing

%put &=SYSVLONG;

prints the version number to the log.

Re: Send Excel Attachment without file path

CharlesFowler72 — Fri, 14 Sep 2018 10:11:07 GMT

im using SAS Enterprise Guide 4.3

Re: Send Excel Attachment without file path

andreas_lds — Fri, 14 Sep 2018 10:25:08 GMT

The version of Enterprise Guide is not tied to a specific sas-version, so please execute the code i posted.

Re: Send Excel Attachment without file path

CharlesFowler72 — Fri, 14 Sep 2018 10:47:34 GMT

1 The SAS System 09:04 Friday, September 14, 2018

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='as above but test';
4 %LET _CLIENTPROJECTPATH='\\GBMLVFILFS05N02\Home3$\fowlchl\Documents\Documents\SAS\RFW.egp';
5 %LET _CLIENTPROJECTNAME='RFW.egp';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
NOTE: Procedures may not support all options or statements for all devices. For details, see the documentation for each procedure.
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport12(ID=EGSR) FILE=EGSR STYLE=Analysis
12 ! STYLESHEET=(URL="file:///V:/VOE06529.100/VFS/SFT_PROGRAM_FILES_X64/SAS92/EnterpriseGuide/4.3/Styles/Analysis.css")
12 ! NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT12(EGSR) Body file: EGSR
13
14 GOPTIONS ACCESSIBLE;
15 %put &=SYSVLONG;
&=SYSVLONG
16
17 GOPTIONS NOACCESSIBLE;
18 %LET _CLIENTTASKLABEL=;
19 %LET _CLIENTPROJECTPATH=;
20 %LET _CLIENTPROJECTNAME=;
21 %LET _SASPROGRAMFILE=;
22
23 ;*';*";*/;quit;run;
24 ODS _ALL_ CLOSE;
25
26
27 QUIT; RUN;
28

Re: Send Excel Attachment without file path

andreas_lds — Fri, 14 Sep 2018 11:40:32 GMT

From your log:

12 ! STYLESHEET=(URL="file:///V:/VOE06529.100/VFS/SFT_PROGRAM_FILES_X64/SAS92/EnterpriseGuide/4.3/Styles/Analysis.css")

This SAS 9.2 and now the bad news: you need Access To PC Files licenced to create xls-files. And with that module you can't create xlsx-files.

Re: Send Excel Attachment without file path

Tom — Fri, 14 Sep 2018 12:32:07 GMT

Since you are running an ancient version of SAS you cannot use the XLSX engine. You could try PROC EXPORT.

Or you might have to resort to producing an XML or HTML file and telling the use to let Excel convert it to a spreadsheet.

Re: Send Excel Attachment without file path

CharlesFowler72 — Fri, 14 Sep 2018 13:54:58 GMT

so based on my current code of

FILENAME mail EMAIL TO='me@company.co.uk>' 
SUBJECT="D Jumpers" CONTENT_TYPE="text/html";
ODS HTML BODY=mail;
PROC PRINT DATA=work.D_Jumpers;
RUN;
ODS HTML CLOSE;

how do I change that to be an attachment rather than body of the email. Again, avoiding any path files.