/* 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 */
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!
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;
/* 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;
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:
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;
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:
😞
@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.
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!
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;
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.