BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bnarang
Calcite | Level 5

Hi All

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

3 REPLIES 3
Robert_Bardos
Fluorite | Level 6

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
SAS Super FREQ

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
bnarang
Calcite | Level 5

Hi Cynthia

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

Regards

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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