topic Re: Tabulate cross variables, change cell color based on value in Excel in ODS and Base Reporting

Tabulate cross variables, change cell color based on value in Excel

jprosenbaum8908 — Fri, 05 Dec 2014 16:37:17 GMT

I am using proc tabulate to cross variables then outputting to an excel sheet using ods tagset.

So I am using a dataset that has a zipcode with some of those zipcodes having a leading zero. When I output to excel using ods tagset, it chops off the zero.

I also want to change the color of the cell based on the value of the cell. I want the cell to have a color if there is no missing value or greater than zero.

Here is a sample set of my data:

data test;

input Areacode Deliverygrp $ weeknumdeliv;

datalines;

01033 H1 02

01033 H1 05

01033 H2 01

01033 H2 30

02213 H1 44

02213 H1 35

10033 H1 04

10033 H1 06

10033 H2 10

10033 H2 29

21226 H2 15

21226 H2 12

21226 H2 03

;

run;

ODS TAGSET.EXCELXP file = 'C:\myfilelocation\testtry3.xls';

proc tabulate data = test;

by deliverygrp;

class areacode weeknumdeliv

classlev areacode/style={tagattr ="00000"};

table areacode, weeknumdeliv*N = " ";

run;

ODS TAGSETS.EXCLXP CLOSE;

Should I use a proc format procedure for the cell color or is this another tagattr type of redefining the output? Or is this something entirely different?

Any suggestions would be great. Thanks all!

I have attached the excel file.

Re: Tabulate cross variables, change cell color based on value in Excel

Haikuo — Fri, 05 Dec 2014 16:53:46 GMT

What you are doing is called "Traffic Lighting", search this term along with SAS, you will get a ton. For example:

http://support.sas.com/resources/papers/proceedings11/290-2011.pdf

Haikuo

Re: Tabulate cross variables, change cell color based on value in Excel

ballardw — Fri, 05 Dec 2014 18:03:46 GMT

To get the leading 0

1) Read the data as Text : input areacode $

2) SORT the data by deliverygrp variable to run as stated

3) Spell TAGSETS.EXCELXP correctly

not TAGSET.EXCELXP

or TAGSETS.EXCLXP

Re: Tabulate cross variables, change cell color based on value in Excel

jprosenbaum8908 — Wed, 10 Dec 2014 15:59:54 GMT

Thanks. I understand that it is traffic lighting. But I am not sure how to change the cell color based on the crossed variables. That is my issue.

Re: Tabulate cross variables, change cell color based on value in Excel

Cynthia_sas — Thu, 11 Dec 2014 15:59:41 GMT

Hi, Assuming that you fix your syntax errors (misspelled destinations, missing PROC SORT step and missing semi-colons), the syntax would be something like this. I am teaching today, so I don't have full access to SAS and the Internet on the same machine. The solution would be something like this:

make a user-defined format:

proc format;

value bck . = 'lightyellow'

1 = 'lightblue'

2 = 'lightgreen'

3-high = 'lavender';

run;

adjust your TABLE statement:

table areacode,
weeknumdeliv*N=" "*{s={background=bck.}};

cynthia

Re: Tabulate cross variables, change cell color based on value in Excel

jprosenbaum8908 — Thu, 11 Dec 2014 16:38:29 GMT

Thanks Cynthia. I had figured it out this morning, doing almost what you did.