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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

5 REPLIES 5
Haikuo
Onyx | Level 15

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

jprosenbaum8908
Calcite | Level 5

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.

ballardw
Super User

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

Cynthia_sas
SAS Super FREQ

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

jprosenbaum8908
Calcite | Level 5

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

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
  • 5 replies
  • 2248 views
  • 0 likes
  • 4 in conversation