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.
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
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
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.
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
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
Thanks Cynthia. I had figured it out this morning, doing almost what you did.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.