Excel Report ( Conditional formatted) as attachment in Email

[bnarang]

Hi All

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

[Robert_Bardos]

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

[Cynthia_sas · Accepted solution]

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;

[bnarang]

Hi Cynthia

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

Regards