BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
MelissaM
Obsidian | Level 7

Screenshot 2022-02-26 073255.jpgHello SAS Community!

 

Objective

Create a table which highlights the cells using the following rules:

 

4cyl cars which begin with "A": color the cells blue

NOT 4cyl cars that do NOT begin with "A": color the cells yellow.

 

 

I’d also like the total (105 for this subset) to show up, if possible.

 

My SAS attempt is below (doesn't work).

My Excel desired output is also displayed.

data cars;
	set sashelp.cars;

if substr(make, 1, 1) in ("A" "B" "C");

if (cylinders = 4) and (substr(make,1,1) = "A") THEN BG1F=0;else
IF (clyinders NE 4) and (substr(make,1,1) ne "A") then BG1F=1;


keep make type origin cylinders;

run;

proc format;
	value 	bg1f	0=blue 1=yellow;
 
run;

proc tabulate data=cars style={  background=bg1f.};
	class make type origin cylinders;
	classlev make;
	tables (all='')*(make)*(type),(origin)*(cylinders)*all/nocellmerge;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

I managed to do something like that with Proc Tabulate one time but the amount of custom coding, pre-summarizing data, writing custom formats for not-actually-values displayed was such that it would be faster to manually set colors. That approach involved creating unique values for the cells that display, by that I mean to display a blue 2 the value was actually like 2.1 and for a yellow 2 it was 2.2 so that a single format could be assigned. Not interested in going there.

 

I also see some serious complications with Proc Report because of the multiple nestings in both row and column and how to address the column values.

 

If I were to attempt such a thing again I would 1) summarize the data first (possibly with Proc Tabulate creating an output data set) and then 2) look at the data step Report Writing Interface which is going to let you examine multiple variables to set properties for cells.

View solution in original post

5 REPLIES 5
sbxkoenk
SAS Super FREQ

Hello,

 

You want an example on color formats?

Proc format;
value $sexfmt
'M'='light blue'
'F'='pink';
run;

ods html path='C:\WorkShop\' body='printout.html';
proc print data=sashelp.class noobs;
where Age = 15;
var Name Height;
var Sex / style=[background=$sexfmt. font_weight=bold];
run;
ods html close;
/* end of program */

Koen

MelissaM
Obsidian | Level 7
It didn't work for me, unfortunately.
I updated my post to show the desired output (in MS Excel), which shows blue & yellow cells.
mkeintz
Jade | Level 19

The OP's traffic lighting needs for highlighting selective cells are not dependent on the values in the cells. Instead, they are dependent on row and column values, so I do not see how a format utilized in a PROC TABULATE can do the job.

 

 I wonder if some of the PROC REPORT experts on this forum could produce the desired results.

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ballardw
Super User

I managed to do something like that with Proc Tabulate one time but the amount of custom coding, pre-summarizing data, writing custom formats for not-actually-values displayed was such that it would be faster to manually set colors. That approach involved creating unique values for the cells that display, by that I mean to display a blue 2 the value was actually like 2.1 and for a yellow 2 it was 2.2 so that a single format could be assigned. Not interested in going there.

 

I also see some serious complications with Proc Report because of the multiple nestings in both row and column and how to address the column values.

 

If I were to attempt such a thing again I would 1) summarize the data first (possibly with Proc Tabulate creating an output data set) and then 2) look at the data step Report Writing Interface which is going to let you examine multiple variables to set properties for cells.

MelissaM
Obsidian | Level 7

You are both correct.
I was able to use formatting to assign values within the cells, but that didn't work for my actual data because:
1. I want the program to be dynamic so the highlighting updates when the data is updated, and
2. For example, this data had multiple cells with 1's. I only wanted to highlight a single cell with a 1.
Here's a link with much more information on the topic:
https://communities.sas.com/t5/SAS-Programming/Colouring-proc-tabulate-columns-by-column-classificat...

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 367 views
  • 1 like
  • 4 in conversation