topic proc report across variable: change column background color in ODS and Base Reporting

proc report across variable: change column background color

gzr2mz39 — Wed, 27 Jul 2011 20:50:08 GMT

I would like to change the background to yellow when ydoi=2010.
ydoi equals 2010 and 2011.

Any ideas?
Thank you.

proc report data=month_tots1 nowd contents="Claims" out=test
style(header)=[font_weight=bold background=CX90D9D7 /*very light bluish green*/];
options missing='';
column mdoi ydoi, (msd_tot o_tot);
define mdoi / group "Month" format=mof. order=data style(column)={just=l};
define ydoi / across "" format=ydoif. order=data;
define msd_tot / 'MSD';
define o_tot / 'OD';
compute mdoi;
if mdoi=. then call define(_row_,"style","style=[background=CXE599A7]");
endcomp;
run;

Re: proc report across variable: change column background color

Cynthia_sas — Thu, 28 Jul 2011 13:16:34 GMT

Hi:

You will have to venture into the world of ACROSS variable naming to do what you want to do. When PROC REPORT builds a report, it will assign ABSOLUTE column numbers to the variables that are underneath an ACROSS variable.

So, for example in the attached screen shot, the abolute column numbers can be determined by knowing that there are 2 possible values for YEAR and you have 2 variables under each year. So _C2_ and _C3_ are the 2010 values and _C4_ and _C5_ are the 2011 values. If you only wanted to highlight the MSD_TOT value for 2010, then you would apply your CALL DEFINE to _C2_. If you wanted to highlight both the MSD_TOT and the O_TOT values for 2010, then you would apply your CALL DEFINE to _C2_ and _C3_.

cynthia

options missing='';
ods listing close;

data month_tots1;
set sashelp.prdsale;
where quarter=1 and region = 'EAST' and
     country = 'CANADA' ;
if year = 1993 then year = 2010;
else if year = 1994 then year = 2011;
mdoi = month;
ydoi = year;
msd_tot = actual;
o_tot = predict;
run;

** change all 4 columns to different colors;
ods html file='c:\temp\across_rep.html' style=sasweb;
proc report data=month_tots1 nowd contents="Claims"
            out=test
   style(header)=[font_weight=bold background=CX90D9D7];
column mdoi ydoi, (msd_tot o_tot);
define mdoi / group "Month" f=monname3. order=data
       style(column)={just=l};
define ydoi / across "" format=4. order=data;
define msd_tot / 'MSD' f=comma8.;
define o_tot / 'OD' f=comma8.;
compute mdoi;
if mdoi=. then
     call define(_row_,"style",
                       "style=[background=CXE599A7]");
endcomp;
compute msd_tot;
call define('_c2_',"style","style=[background=yellow]");
call define('_c4_',"style","style=[background=cyan]");
endcomp;
compute o_tot;
call define('_c3_',"style","style=[background=pink]");
call define('_c5_',"style","style=[background=green]");
endcomp;
run;
ods html close;

** change 2010 columns to yellow;
** change 2011 columns to cyan;

ods html file='c:\temp\across_year.html' style=sasweb;
proc report data=month_tots1 nowd contents="Claims"
     style(header)=[font_weight=bold background=CX90D9D7];
column mdoi ydoi, (msd_tot o_tot);
define mdoi / group "Month" f=monname3. order=data
       style(column)={just=l};
define ydoi / across "" format=4. order=data;
define msd_tot / 'MSD' f=comma8.;
define o_tot / 'OD' f=comma8.;
compute mdoi;
if mdoi=. then
     call define(_row_,"style",
                       "style=[background=CXE599A7]");
endcomp;

** _c2_, _c3_ are 2010;
** _c4_, _c5_ are 2011;
compute msd_tot;
call define('_c2_',"style","style=[background=yellow]");
call define('_c4_',"style","style=[background=cyan]");
endcomp;
compute o_tot;
call define('_c3_',"style","style=[background=yellow]");
call define('_c5_',"style","style=[background=cyan]");
endcomp;
run;

ods html close;

Re: proc report across variable: change column background color

gzr2mz39 — Thu, 28 Jul 2011 16:46:05 GMT

Is there any way to avoid overriding this compute block:

compute mdoi;
if mdoi=. then call define(_row_,"style","style=[background=CXE599A7]" /*light pink*/);
endcomp;

other than creating compute blocks like this for each variable where I want to change the background color of the column (where ydoi=2010)?

compute msd_tot;
call define('_c6_',"style","style=[background=CXECEDEC]");
if mdoi=. then call define('_c6_',"style","style=[background=CXE599A7]");
endcomp;

Thank you.

Re: proc report across variable: change column background color

Cynthia_sas — Thu, 28 Jul 2011 18:09:09 GMT

Most of the methods boil down to some kind of respecification like this or logic change. But, you could specify things differently. I don't have any missing values for my version of the MDOI variable, but if I use logic to highlight the row if MDOI = "Mar" with my fake data, I do get the entire row for MDOI='Mar' as pink. The trick is avoiding any possible style "collisions" by making sure that you only change the row to pink when MDOI is some value and flipping the logic for the ACROSS items.

cynthia

compute mdoi;

if put(mdoi,monname3.)='Mar' then

call define(_row_,"style",

"style=[background=CXE599A7]");

endcomp;

** _c2_, _c3_ are 2010;

** _c4_, _c5_ are 2011;

compute msd_tot;

if put(mdoi,monname3.)ne 'Mar' then do;

call define('_c2_',"style","style=[background=yellow]");

call define('_c4_',"style","style=[background=cyan]");

end;

endcomp;

compute o_tot;

if put(mdoi,monname3.)ne 'Mar' then do;

call define('_c3_',"style","style=[background=yellow]");

call define('_c5_',"style","style=[background=cyan]");

end;

endcomp;