topic Re: Last column in Proc Tabulate must be in Yellow color in SAS Programming

Last column in Proc Tabulate must be in Yellow color

SAS_Question — Sat, 09 Jul 2022 21:44:52 GMT

/* HAVE */
proc format;
   value myfmt 12='12';
   value mycol 12='Yellow';
run;

data have(keep=Make Model Cylinders);
	set sashelp.cars; 
	if make in ('Audi','BMW','Volkswagen','Mercedes-Benz') then output;
run;

proc sort data=have;
	by Make Model;
run;

PROC TABULATE DATA=WORK.HAVE;
	CLASS Cylinders Make Model;
	classlev Cylinders / s={background=mycol.};
	keyword N / s=< parent >;
    format Cylinders myfmt.;
	keylabel all="Total";
	TABLE 
		(Make * (Model all = 'Total' )	all = 'Total' ),
		Cylinders all = 'Total' ;
RUN;

/* WANT */

WANT the column like this...

I Want the whole last column to be in yellow, not only the headers...

How do I do that?

Please see the picture. I want it to be like that... but i'm stuck!

Thanks for the help or tips!

Re: Last column in Proc Tabulate must be in Yellow color

Ksharp — Sun, 10 Jul 2022 11:07:26 GMT

/* HAVE */
proc format;
   value myfmt 12='12';
   value mycol 12='Yellow';
run;

data have(keep=Make Model Cylinders);
	set sashelp.cars; 
	if make in ('Audi','BMW','Volkswagen','Mercedes-Benz') then output;
run;

proc sort data=have;
	by Make Model;
run;

PROC TABULATE DATA=WORK.HAVE;
	CLASS Cylinders Make Model;
	classlev Cylinders / s={background=mycol.};
	keyword N / s=< parent >;
    format Cylinders myfmt.;
	keylabel all="Total";
	TABLE 
		(Make * (Model all = 'Total' )	all = 'Total' ),
		Cylinders*n*{style=<parent>} all = 'Total' ;
RUN;

Re: Last column in Proc Tabulate must be in Yellow color

SAS_Question — Sun, 10 Jul 2022 11:56:11 GMT

Thanks @Ksharp!
We are almost there... 🙂
Now if I select only Mercedes, like in the code below, and it (the N?) has also a value with '12' in another column,

than that field in the other column will get the yellow color also ... how do I supress that?

Look at mij output, to see what I mean:

proc format;
   value myfmt 12=12;
   value mycol 12='Yellow';
run;

data have(keep=Make Model Cylinders);
	set sashelp.cars; 
	if make in ('Mercedes-Benz') then output;
run;

proc sort data=have;
	by Make Model;
run;

PROC TABULATE DATA=WORK.HAVE;
	CLASS Cylinders Make Model;
	classlev Cylinders / s={background=mycol.};
	keyword N / s=< parent >;
    format Cylinders myfmt.;
	keylabel all="Total";
	TABLE 
		(Make * (Model all = 'Total' )	all = 'Total' ),
		Cylinders*n*{style=<parent>};
RUN;

Will produce:

Re: Last column in Proc Tabulate must be in Yellow color

Ksharp — Sun, 10 Jul 2022 12:57:15 GMT

OK. You want this ?

proc format;
   value mycol 12='Yellow';
   value fmt low-high='white';
run;

data have(keep=Make Model Cylinders);
	set sashelp.cars; 
	if make in ('Mercedes-Benz') then output;
run;

proc sort data=have;
	by Make Model;
run;

PROC TABULATE DATA=WORK.HAVE style=header;
	CLASS Cylinders Make Model;
	classlev Cylinders / s={background=mycol.};
	keyword N / s=< parent >;
	keylabel all="Total";
	TABLE 
		(Make * (Model all ={Label='Total' style={BACKGROUND=lightred}} * {STYLE={BACKGROUND=lightred}} )	
ALL={Label='Total' style={BACKGROUND=lightred}} * {STYLE={BACKGROUND=lightred}} ),
		Cylinders*n*{style=<parent>}/style_precedence=row;
RUN;

Re: Last column in Proc Tabulate must be in Yellow color

SAS_Question — Sun, 10 Jul 2022 13:38:10 GMT

That cheating 😉

It works good as long as the data remains as is... right?

Suppose the data becomes this:


proc format;
   value mycol 12='Yellow';
run;

data have(keep=Make Model Cylinders);
	set sashelp.cars;
 	if make in ('Mercedes-Benz') then output;
run;

proc sql;
    insert into work.have
        values("Mercedes-Benz", "E501", 8)
	    values("Mercedes-Benz", "E501", 8)
	    values("Mercedes-Benz", "E501", 8)
	    values("Mercedes-Benz", "E501", 8)
        values("Mercedes-Benz", "E501", 8)
	    values("Mercedes-Benz", "E501", 8)
	    values("Mercedes-Benz", "E501", 8)
	    values("Mercedes-Benz", "E501", 8)
        values("Mercedes-Benz", "E501", 8)
	    values("Mercedes-Benz", "E501", 8)
	    values("Mercedes-Benz", "E501", 8)
		values("Mercedes-Benz", "E501", 8);
quit;

proc sort data=have;
	by Make Model;
run;

Then your code won't work. Look:

PROC TABULATE DATA=WORK.HAVE style=header;
	CLASS Cylinders Make Model;
	classlev Cylinders / s={background=mycol.};
	keyword N / s=< parent >;
	keylabel all="Total";
	TABLE 
		(Make * (Model all ={Label='Total' style={BACKGROUND=lightred}} * {STYLE={BACKGROUND=lightred}} )	
ALL={Label='Total' style={BACKGROUND=lightred}} * {STYLE={BACKGROUND=lightred}} ),
		Cylinders*n*{style=<parent>}/style_precedence=row;
RUN;

Will now give:

😞

Re: Last column in Proc Tabulate must be in Yellow color

ballardw — Sun, 10 Jul 2022 19:36:53 GMT

@SAS_Question wrote:

That cheating 😉

It works good as long as the data remains as is... right?

Actually I would say your question is "cheating". You have not provided a general RULE but single example values. So anything other than exactly your example values does not a good chance at a clean general solution.

One problem is assigning a format to general statistic, n. That can occur in multiple places so is likely not a good "solution" except to specific data.

If you want an extremely specific control on the appearance you may have to summarize the data and use a different approach to creating the output such as the Report Writing Interface in a data step that allows all sorts of conditional statements to set styles based on variable/value combinations.

Re: Last column in Proc Tabulate must be in Yellow color

SAS_Question — Sun, 10 Jul 2022 21:49:01 GMT

You are right @ballardw !

I thought there was something for the proc tabulate that I might have missed, because of my struggle with this issue.

But even if it is a very wonderful tool (the proc tabulate) it lacks something (like coloring the way YOU want it, not the way the data wants it).

So maybe I should do a Proc Report research for this job..

If you have any tips where to start, it might help!

Thanks anyways!

Re: Last column in Proc Tabulate must be in Yellow color

Ksharp — Mon, 11 Jul 2022 11:46:56 GMT

OK. I think you need to change the type of variable "Cylinders" to avoid the conflict with keyword "N" .

And try this one:

data have(keep=Make Model Cylinders);
 set sashelp.cars;
  if make in ('Mercedes-Benz') then output;
run;
proc sql;
    insert into work.have
        values("Mercedes-Benz", "E501", 8)
     values("Mercedes-Benz", "E501", 8)
     values("Mercedes-Benz", "E501", 8)
     values("Mercedes-Benz", "E501", 8)
        values("Mercedes-Benz", "E501", 8)
     values("Mercedes-Benz", "E501", 8)
     values("Mercedes-Benz", "E501", 8)
     values("Mercedes-Benz", "E501", 8)
        values("Mercedes-Benz", "E501", 8)
     values("Mercedes-Benz", "E501", 8)
     values("Mercedes-Benz", "E501", 8)
  values("Mercedes-Benz", "E501", 8);
quit;
proc sort data=have;
 by Make Model;
run;



proc format;
   value $ mycol '12'='Yellow';
run;
data have;
 set have;
 _Cylinders=put(Cylinders,best. -l);
 drop Cylinders;
run;

PROC TABULATE DATA=WORK.HAVE order=data;
 CLASS _Cylinders Make Model;
 classlev _Cylinders / s={background=$mycol.} ;
 keyword N / s=< parent >;
 keylabel all="Total";
 TABLE 
  (Make * (Model all = 'Total' ) all = 'Total' ),
  _Cylinders*n*{style=<parent>};
RUN;