The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

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 02

01033 H1 05

01033 H2 01

01033 H2 01

01033 H2 30

02213 H1 44

02213 H1 35

02213 H1 35

02213 H1 35

10033 H1 04

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.


Accepted Solutions
Solution
‎12-11-2014 10:59 AM
SAS Super FREQ
Posts: 8,740

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

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

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

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

Occasional Contributor
Posts: 14

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

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.

Super User
Posts: 10,474

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

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

Solution
‎12-11-2014 10:59 AM
SAS Super FREQ
Posts: 8,740

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

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

Occasional Contributor
Posts: 14

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 969 views
  • 0 likes
  • 4 in conversation