The SAS Output Delivery System and reporting techniques

Format cells in Excel using Excelxp Tagset

Reply
N/A
Posts: 0

Format cells in Excel using Excelxp Tagset

Hi,
I would like to give a different background color / font color to cells that have a ZERO value in the columns 2,3,6, and 7 of my output sheet. I'm using ExcelXP tagset. I familiar with proc template approach where I can make changes to the styles and apply that new style in the proc print statement of my ODS Tagset part of the code. But I would like to do have a different color only if the value is ZERO.Can some one help me on how to start with here. I was searching this forum to see if I can find a solution but all results in my search were using DDE method to write data into excel and formating which look complicated for me. So I felt like giving a shot here before I start messing up with the DDE approach.

Thanks,
Matt
SAS Super FREQ
Posts: 8,743

Re: Format cells in Excel using Excelxp Tagset

Hi:
With a simple STYLE= override (as shown below in PROC PRINT), you can conditionally highlight (or "trafficlight") values in your ODS output -- whether ExcelXP, HTML, RTF or PDF. As long as the destination supports a change to the BACKGROUND attribute, you can change the BACKGROUND color of the cell. (You could also change the FOREGROUND or any of the font attributes, as well.)
[pre]

proc format;
value agebk 12-13 = 'Yellow'
other = 'white';
value htbk 60-63='cxdddddd'
other = 'white';
run;

ods tagsets.excelxp file='c:\temp\class.xls' style=sasweb;

proc print data=sashelp.class;
var name sex;
var age / style={background=agebk.};
var height / style={background=htbk.};
run;
ods tagsets.excelxp close;
[/pre]

The key is understanding what style attributes you can change (search in the documentation for the phrase "Style Attributes and Their Values").

And then, if you're using PROC PRINT or PROC TABULATE, you can use a simple user-defined format to change the background color (or other attribute), as shown in the program above. I only change one attribute, but if I wanted to change FOREGROUND as well as BACKGROUND, I would just need another user-defined format for the values/conditions which would be applied, based on the value in the cell.
[pre]
var age / style={background=agebk. foreground=agefr. font_weight=fw.};
[/pre]

...where the AGEFR. and FW. formats were defined to hold the values that needed to be set based on the value of AGE. Or, I could just change the background color conditionally using the format and make the font_weight bold for EVERY value of AGE:
[pre]
var age / style={background=agebk. font_weight=bold};
[/pre]

If you had a more complex condition (name="Mary" and age gt 13), then you could not use a format for that type of traffic lighting, you'd have to use PROC REPORT and a CALL DEFINE statement to perform the conditional highlighting. But for simple changes, such as what you mention, then the STYLE= approach should work for you in PRINT, REPORT or TABULATE.

There is just one thing. You refer to columns 2, 3, 6 and 7 -- those must be variables from your SAS dataset -- you will have to refer to them by name in the VAR statement. Unless you want ALL numeric variables to be highlighted, using a technique shown at the bottom of the post (where I had to make some dummy data so there were zeroes in some of the numeric variables).

cynthia
[pre]

data badclass;
set sashelp.class;
if name = 'John' then age = 0;
else if name = 'William' then height = 0;
else if name = 'Mary' then weight = 0;
else if name = 'Alfred' then do;
age = 0;
height = 0;
weight = 0;
end;
run;

proc format;
value anybk 0 = 'pink'
other = 'white';
run;

ods tagsets.excelxp file='c:\temp\badclass.xls' style=sasweb;

proc print data=badclass;
var name sex;
var _numeric_/ style={background=anybk.};
run;
ods tagsets.excelxp close;
[/pre]
N/A
Posts: 0

Re: Format cells in Excel using Excelxp Tagset

Cynthia,
Thanks for the information. It was helpful. Now I would like to implement something similar to my Proc template Statement.

Here is my Proc Template which creates wingding font on my excel output. Values with 1 gets a grey circle and values with ZERO gets a RED circle. I'm looking for a yellow background along with that red circle foreground. There should be some option with that tagattr statement for background too. I couldn't find any papers on that approch.

proc template;
define style styles.XLsansPrinter;
parent = styles.sansPrinter;
style header from header /
just = center;
style rowheader from rowheader /
just = center;
style data from data /
just = center;
style data_bullet from data /
tagattr='format:[color16]\m;;[RED]\l'
font_face=Wingdings;
end;Run;Quit;
SAS Super FREQ
Posts: 8,743

Re: Format cells in Excel using Excelxp Tagset

Hi:
What goes in TAGATTR is pure Microsoft formatting information. I know how to change the background using SAS and ODS methods using the STYLE= override. I don't have a clue about how to change the background color of a cell using TAGATTR methods. If I don't use the STYLE= method, I change background color of cells with point and click methods once I open the file in Excel.

The string that's in the TAGATTR attribute is a MICROSOFT formatting instruction. It is possible that
1) the background color is specified using property values that have not been explored in any papers (a question for Tech Support whether there is a TAGATTR method for changing background color)
OR
2) the background color for the cells goes in a different place in the Spreadsheet Markup Language XML specification, as set by Microsoft. (also a question for Tech Support -- if there is no TAGATTR method for changing background color, is there a STYLE template method that would work)

I'm not sure you can change background color via TAGATTR instructions. You may need to work with Tech Support on this question. BTW, I assume that you have tried this:
[pre]
style data_bullet from data /
tagattr='format:[color16]\m;;[RED]\l'
background=yellow
font_face=Wingdings;
[/pre]

....and that it does not work????
cynthia
N/A
Posts: 0

Re: Format cells in Excel using Excelxp Tagset

Cynthia,
I did try that which give a yellow back ground no matter what value is in the cell.
I saw papers where they use this Microsoft attribute function to generate a foreground color but I saw none that use the same approach for getting background.

I'll check with the Tech support on this and post back their response here.


Thanks for you prompt response.

Matt
SAS Super FREQ
Posts: 8,743

Re: Format cells in Excel using Excelxp Tagset

Hi:
If you were using PROC REPORT with CALL DEFINE, you could "swap" between 2 different style elements, as shown in this example. It doesn't use TAGATTR or WINGDINGS fonts, but perhaps this will get you closer to what you want.

cynthia
[pre]
ods path work.temp(update)
sashelp.tmplmst(read);

proc template;
define style styles.new_sw;
parent = styles.sasweb;
style data1 from data /
background=white
font_weight=bold
foreground=red
font_face="Courier New";
style data2 from data /
background=pink
foreground=purple
font_weight=bold
font_face="SAS Monospace";
end;
Run;


ods tagsets.excelxp file='c:\temp\trytemp.xls' style=new_sw;

proc report data=sashelp.class nowd;
title 'Use CALL DEFINE to switch between 2 style elements';
column name sex age height;
define name / order;
define sex / display;
define age / display;
define height /display;
compute age;
if age in (12,13) then do;
call define(_col_, "style", "style=data2");
end;
else call define(_col_, "style", "style=data1");
endcomp;
run;
ods tagsets.excelxp close;

[/pre]
N/A
Posts: 0

Re: Format cells in Excel using Excelxp Tagset

Cynthia,
I never used much of PROC REPORT for my reporting and now this approach seams interesting. I have a question here. Is it possible for me do check between variables and then apply styles using the Call define statement. say for instance I want to give a yellow background for cells in both AGE and DISEASE columns if age < 50 and disease=0 condition satisfies; and a WHITE background to the cells if age > 50 and disease=1.

Regards,
MAtt
SAS Super FREQ
Posts: 8,743

Re: Format cells in Excel using Excelxp Tagset

Hi:
Yes, this is one of the strengths of PROC REPORT and CALL DEFINE. Once you know how PROC REPORT works -- specifically the COMPUTE block -- one of the strengths of PROC REPORT is that you can test multiple conditions. So, for example, I could have:
[pre]
compute age;
if age in (12,13) and name in ('John', 'Mary') then do;
call define(_col_, "style", "style=data2");
end;
else call define(_col_, "style", "style=data1");
endcomp;
[/pre]

There are a few caveats...in order for me to test NAME in the COMPUTE block for AGE, NAME has to be listed -before- AGE in the COLUMN statement:
column name age height weight

Also, in the COMPUTE block for HEIGHT, I can test values of NAME and AGE, which appear before HEIGHT in the COLUMN statement. However, in the COMPUTE block for AGE, I could NOT test the value of WEIGHT (for example).

And, depending on whether a variable was a GROUP or ORDER usage, I might need to "grab" a value of the variable BEFORE or AFTER the group (in order to use the value in a test) -- but this is only because PROC REPORT, by default, suppresses the repetitious display of the same values for GROUP or ORDER usage report items.

The key to how PROC REPORT operates is to understand that your data is going into PROC REPORT and from your data, PROC REPORT is building either a "detail" report row (one report row for every observation) or a "summary" report row (one report row represents the collapsing of a group of observations). PROC REPORT builds a report row one row at a time, building each report row by placing report items (what's listed in the COLUMN statement) from left to right on the report row. So you can't test NAME before it has been placed on the report row. Once you understand how PROC REPORT operates, all kinds of neat reports are possible. For example, this ODS HTML program I had shows the test of multiple conditions and also shows the difference between a "detail" and a "summary" report.
[pre]
ods html file='c:\temp\showrep.html' style=sasweb;

proc report data=sashelp.class nowd;
title '1) Detail Report';
column sex name age height weight silly;
define sex / order 'Gender';
define name / order;
define age / sum;
define height /sum;
define weight /sum;
define silly / computed f=comma16.3;
break after sex / summarize;
rbreak after / summarize;
compute silly;
silly = age.sum * height.sum * weight.sum;
if silly gt 80000 and name in ('Janet', 'Alfred') then do;
call define("name",'style','style={background=yellow font_weight=bold}') ;
call define(_COL_,'style','style={background=red font_weight=bold}') ;
end;
endcomp;
compute age;
if name in ('Mary', 'Philip') and age le 16 then
call define(_ROW_,'style','style={background=lightblue}');
endcomp;
run;

** now what if you only want to see the summary line for each gender?;
proc report data=sashelp.class nowd;
title '2) Summary Report';
column sex age height weight silly;
define sex / group 'Gender';
define age / sum noprint;
define height /sum noprint;
define weight /sum noprint;
define silly / computed f=comma16.3;
rbreak after / summarize;
compute silly;
silly = age.sum * height.sum * weight.sum;
if sex = 'F' then
call define(_ROW_,'style','style={background=pink font_weight=bold}');
else if sex = 'M' then
call define(_ROW_,'style','style={background=lightblue font_weight=bold}');
else if _break_ = '_RBREAK_' then
call define(_ROW_,'style','style={background=yellow font_weight=bold}');
endcomp;
run;

ods html close;
[/pre]
N/A
Posts: 0

Re: Format cells in Excel using Excelxp Tagset

Hi,
I was working the same approach as you mentioned and could get most of my work done except for one. I have situation where I would like to flag EKG and PHY columns if they have a different values compared to HV_TEL and also flag the HV_Tel column.

Say if a person A, have a same value in both HV_TEL and EKG but a different values in PHY then I would like to flag HV_TEL and PHY leaving EKG with a different color.

Here is my code. for some reason HV_TEL doesn't get flagged even tough HV_TEL doesn't match with PHY but gets flagged if HV_TEL is different to EKG.I do have the order right in the column statement.

ODS tagsets.ExcelXP body='C:\Documents and Settings\penmetsaa\My Documents\Desktop\HV_REPORT_1.xml' style=sasweb;
footnote h=10pt f=TimesRoman j=l "%sysfunc(today(),mmddyy10.)";
ods tagsets.ExcelXP options(embedded_titles='yes' sheet_name='TELEFORMS' row_heights='40,15' absolute_column_width='9'
Frozen_rowheaders='4' FROZEN_HEADERS='4' ZOOM='80' fittopage='yes' scale='80' autofilter='5');

proc report data=Teleforms nowd;
column IDNO VI NAME ODV HV_TEL EKG PHY ;
define IDNO / order 'BLSAID';
define VI / order 'VISIT';
define NAME / display 'NAME';
define ODV / display 'Date of Visit';
define HV_TEL / display 'PACK - T';
define EKG / display 'EKG -DB';
define PHY / display 'PHYSICAL EXAM -DB';

compute EKG;
if EKG ne HV_TEL then do;
call define("HV_TEL",'style','style={background=red font_weight=bold }') ;
call define(_COL_,'style','style={background=red font_weight=bold}') ;
end;else if EKG = HV_TEL then do;
call define("HV_TEL",'style','style={background=white }') ;
call define(_COL_,'style','style={background=white }') ;
end;
endcomp;

compute PHY;
if PHY ne HV_TEL then do;
call define("HV_TEL",'style','style={background=red font_weight=bold }') ;
call define(_COL_,'style','style={background=red font_weight=bold}') ;
end;else if PHY = HV_TEL then do;
call define("HV_TEL",'style','style={background=white }') ;
call define(_COL_,'style','style={background=white }') ;
end;
endcomp;

title1 bold font=times color=red justify=center h=5 'HOME VISIT - TELEFORMS REPORT ';
title2 bold font=times bcolor=black color=yellow justify=center italic h=2 '1 - present; 0 - missing';

Run;Quit;
ods tagsets.ExcelXP close;



Regards,
MAtt
SAS Super FREQ
Posts: 8,743

Re: Format cells in Excel using Excelxp Tagset

Hi:
Well, you could change ALL the variables in the COMPUTE block for PHY. That's because when you're in the COMPUTE block for PHY (at the end of the COLUMN statement), you could change any item that preceded PHY in the COLUMN statement.

This might allow you to build a more straighforward COMPUTE block. When you are in the COMPUTE block for PHY, you can also test the values for EKG and HV_TEL. Something like what's shown below. Note that I wasn't sure of all the conditional formatting you wanted to apply, so the second half of the COMPUTE block is commented out.
[pre]
compute PHY;
if PHY=HV_TEL then do;
if EKG = HV_TEL then do;
call define("HV_TEL",'style','style={background=white }') ;
call define("EKG",'style','style={background=white}');
call define(_COL_,'style','style={background=white }') ;
end;
else if EKG ne HV_TEL then do;
call define("HV_TEL",'style','style={background=red font_weight=bold }') ;
call define("EKG",'style','style={background=pink font_weight=bold}') ;
call define(_COL_,'style','style={background=red font_weight=bold}') ;
end;
end;
/*
else if PHY ne HV_TEL then do;
if EKG = HV_TEL then do;
call define("HV_TEL",'style','style={???}') ;
call define("EKG",'style','style={???}') ;
call define(_COL_,'style','style={???}') ;
end;
else if EKG ne HV_TEL then do;
call define("HV_TEL",'style','style={???}') ;
call define("EKG",'style','style={???}') ;
call define(_COL_,'style','style={???}') ;
end;
end;
*/
endcomp;
[/pre]

cynthia
Regular Learner
Posts: 1

Format cells in Excel using ODS EXCEL / Excelxp Tagset.

Hi,

 

I am using ODS Excel / tagsets.excelxp to generate .xlsx output using SAS.

My code looks like - 

 

proc report data=sashelp.class nowd;
column ('Group 1' sex name age) ('Group 2' height weight) ;
define sex / order 'Gender';
define name / order;

.

.

There are 2 queries on this - 

 1. I want to have a different background colour for grouped cells 'Group 1' and 'Group 2'.  Say BLUE for group1 and RED for group2. Also, the next row would have different colours.

Please let me know how can I do this?

 

2. I also want to display group 1 and group 2 as GROUPED columns in Excel output.

If I was to do it in excel I would do it as - 

Select columns SEX, NAME, AGE

Goto Menu -> Data -> Group -> Group.

This gives me a horizontal line with "-" sign. When it is pressed, these columns are hidden and I see a "+" sign. If i press + then these columns are unhide.

 

Attached .xlsx file gives more clarity about my desired output.

 

I want to see this grouping in excel output derived using SAS.

Could you please help me to get this output please ?

 

Thanks in advance for your inputs.

 

Thanks,

 

Ajay Yeola.

 

Ask a Question
Discussion stats
  • 10 replies
  • 3233 views
  • 0 likes
  • 3 in conversation