DATA Step, Macro, Functions and more

Excel Report ( Conditional formatted) as attachment in Email

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 86
Accepted Solution

Excel Report ( Conditional formatted) as attachment in Email

Hi All

Is it possible to produce an Excel report from SAS with conditional formatting and be attached in email using sas?


Accepted Solutions
Solution
‎12-09-2012 11:25 AM
SAS Super FREQ
Posts: 8,739

Re: Excel Report ( Conditional formatted) as attachment in Email

And, there have been many previous forum postings on both trafficlighting and emailing. Here's a simple example, using PROC REPORT to get you started.

First, you need to create either the CSV, the HTML or the XML file to email. When you need highlighting and colors added to your output, you can't use CSV as your destination of choice. So that leaves creating either a Microsoft HTML file or a Microsoft XML file that Excel can open and render. The example below uses ODS MSOFFICE2K, which creates Microsoft-friendly HTML. I have named the file with a .XLS file extension so that it automatically opens in Excel when someone double clicks on the file. See the example of the output in the screenshot.

proc format;

  value agef 11-12='yellow'

             13-14='cyan'

             15-16='cxdddddd';

run;

ods msoffice2k file='c:\temp\hilite.xls' style=sasweb;

proc report data=sashelp.class nowd;

title 'Highlighting Conditionally';

column name age height weight sex;

define name / order;

define age / display

        style(column)={background=agef.};

define height / display;

define weight / display;

define sex / display;

compute sex;

   if substr(name,1,1) = 'J' and

      sex = 'F' then do;

      call define('name','style','style={background=pink}');

      call define(_col_,'style','style={background=pink}');

   end;

   else if substr(name,1,1) = 'J' and

      sex = 'M' then do;

      call define('name','style','style={background=lightblue}');

      call define(_col_,'style','style={background=lightblue}');

   end;

  endcomp;

run;

ods msoffice2k close;

You will find many examples of emailing in the forums. My recommendation would be 1) create the HTML file (named XLS) and then 2) draft your email and send the file created in #1 as an attachment to the mail. The body of the mail should warn the recepients that they will probably see an Excel message that will "warn" them the file extension is not in sync with the contents of the file (which is technically true -- an HTML file is not a true binary .XLS file). They will just need to click 'Yes' on the message to open the file.

cynthia

Then, to email, follow this model:


**1) make file;
ods msoffice2k body='c:\temp\class.xls' rs=none style=sasweb;

... your code ...

ods msoffice2k close;
 
**2) Now send the file as an attachment;
filename doemail email
    to=('one.person@sas.com' 'another.person@sas.com')
    from='ima.programmer@sas.com'
    cc=('also.interested@sas.com')
    subject='Open this report in Excel'
    attach='c:\temp\class.xls';
                                
data _null_;
    file doemail;
    put 'This is a test email with an HTML attachment.';
    put 'However, I named the file as .XLS so you could see it';
    put 'in Excel. If you get a popup window from Excel,';
    put 'just click "Yes" to open the file';
run;


Hilite_examp.png

View solution in original post


All Replies
Frequent Contributor
Posts: 106

Re: Excel Report ( Conditional formatted) as attachment in Email

Undoubtedly one of the best resources for questions of this type: Chevell Parker from SAS

Read e.g.  http://www2.sas.com/proceedings/sugi31/091-31.pdf  having a small section on emailing HTML documents directly from SAS.

For a more refined search start at  Lex Jansen's Homepage by entering some of the buzzwords your are looking for in its Search box.

Robert

Solution
‎12-09-2012 11:25 AM
SAS Super FREQ
Posts: 8,739

Re: Excel Report ( Conditional formatted) as attachment in Email

And, there have been many previous forum postings on both trafficlighting and emailing. Here's a simple example, using PROC REPORT to get you started.

First, you need to create either the CSV, the HTML or the XML file to email. When you need highlighting and colors added to your output, you can't use CSV as your destination of choice. So that leaves creating either a Microsoft HTML file or a Microsoft XML file that Excel can open and render. The example below uses ODS MSOFFICE2K, which creates Microsoft-friendly HTML. I have named the file with a .XLS file extension so that it automatically opens in Excel when someone double clicks on the file. See the example of the output in the screenshot.

proc format;

  value agef 11-12='yellow'

             13-14='cyan'

             15-16='cxdddddd';

run;

ods msoffice2k file='c:\temp\hilite.xls' style=sasweb;

proc report data=sashelp.class nowd;

title 'Highlighting Conditionally';

column name age height weight sex;

define name / order;

define age / display

        style(column)={background=agef.};

define height / display;

define weight / display;

define sex / display;

compute sex;

   if substr(name,1,1) = 'J' and

      sex = 'F' then do;

      call define('name','style','style={background=pink}');

      call define(_col_,'style','style={background=pink}');

   end;

   else if substr(name,1,1) = 'J' and

      sex = 'M' then do;

      call define('name','style','style={background=lightblue}');

      call define(_col_,'style','style={background=lightblue}');

   end;

  endcomp;

run;

ods msoffice2k close;

You will find many examples of emailing in the forums. My recommendation would be 1) create the HTML file (named XLS) and then 2) draft your email and send the file created in #1 as an attachment to the mail. The body of the mail should warn the recepients that they will probably see an Excel message that will "warn" them the file extension is not in sync with the contents of the file (which is technically true -- an HTML file is not a true binary .XLS file). They will just need to click 'Yes' on the message to open the file.

cynthia

Then, to email, follow this model:


**1) make file;
ods msoffice2k body='c:\temp\class.xls' rs=none style=sasweb;

... your code ...

ods msoffice2k close;
 
**2) Now send the file as an attachment;
filename doemail email
    to=('one.person@sas.com' 'another.person@sas.com')
    from='ima.programmer@sas.com'
    cc=('also.interested@sas.com')
    subject='Open this report in Excel'
    attach='c:\temp\class.xls';
                                
data _null_;
    file doemail;
    put 'This is a test email with an HTML attachment.';
    put 'However, I named the file as .XLS so you could see it';
    put 'in Excel. If you get a popup window from Excel,';
    put 'just click "Yes" to open the file';
run;


Hilite_examp.png
Frequent Contributor
Posts: 86

Re: Excel Report ( Conditional formatted) as attachment in Email

Hi Cynthia

Thank you so much for such detailed explanation. I am very thankful to you.

Regards

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 2585 views
  • 3 likes
  • 3 in conversation