PROC REPORT: Conditional formatting of columns of across variable

[sarunas]

Hi,

is it possible to do a conditional formatting of columns that appear on the report from ACROSS variable ? By conditional formatting I mean selecting only certain columns by their names (values of ACROSS variable) in the IF statement of COMPUTE block.

The other problems is referencing the column to apply the format in CALL DEFINE. The number of columns can vary depending on number of distinct values of the ACROSS variable, therefore referencing by _C#_ doesn't work. Again it would be good to reference by the column name if that is possible.

Thanks a lot for your help.

[Cynthia_sas]

Hi:
There have been many previous forum postings on "traffic lighting" with PROC REPORT (and PRINT and TABULATE). PROC REPORT and CALL DEFINE and ACROSS processing -do- allow you to traffic light only certain items displayed using ACROSS items -- however, PROC REPORT does NOT allow you to use the simple item name. So if you had something like this:
[pre]
column sex age,height;
define sex / group;
define age / across;
define height / mean;
[/pre]

...then to apply highlighting to some values of AGE and not others would mean that you would need to figure out the column numbers assigned to the values for HEIGHT underneath each value of AGE. What you're referencing in the above scenario is not just 1 report item anymore -- it is the average HEIGHT for each AGE that is being referenced for each cell that you want to style or format with the CALL DEFINE.

So you will need to either:
1) figure out the max number of ACROSS items you could have and code accordingly (SAS Macro can help here)
2) pre-summarize and "flatten" your file -- such as with TRANSPOSE, so that the ages become column names and then use those column names with PROC REPORT to do your CALL DEFINE
3) use a more complicated macro solution, where you figure the number of ACROSS items you will have on any single run of the report and then have the macro solution issue the appropriate CALL DEFINE syntax for you, based on criteria that you send your macro program

Previous forum postings have referred to this paper, which outlines (on pages 12, 13 and 14) a macro method for determining the number of items that will be under an ACROSS variable:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

So, the bottom line is that to use PROC REPORT, you have "think" like PROC REPORT. When you have ACROSS items, there is a pre-processing phase that REPORT undergoes in which any items used with the ACROSS scenario are given absolute column #s of the form _c2_, _c3_, etc. The rules of PROC REPORT are that you MUST use the absolute column numbers when referencing specific columns in a COMPUTE block. The code below shows some default behaviors and some examples of referencing in a COMPUTE block, when HEIGHT is placed under AGE.

cynthia
[pre]
ods listing close;

ods html file='c:\temp\across1.html' style=sasweb;
** Default;
proc report data=sashelp.class nowd;
title '1) default appearance';
column sex age,height;
define sex / group;
define age / across;
define height / mean;
run;
ods html close;

** User-defined format for all HEIGHT values;
proc format;
value ah low-55.999 = 'pink'
56-63.999 = 'cyan'
64-69.999 = 'yellow'
70-high = 'verylightblue'
other = 'cxdddddd';
run;

ods html file='c:\temp\across2.html' style=sasweb;
proc report data=sashelp.class nowd;
title '2) With User-Defined Format';
column sex age,height;
define sex / group;
define age / across ;
define height / mean style(column)={background=ah.};
run;
ods html close;


** With CALL DEFINE for ALL HEIGHT columns (no special style for WEIGHT);
ods html file='c:\temp\across3.html' style=sasweb;
proc report data=sashelp.class nowd;
title '3) With CALL DEFINE Format for HEIGHT';
title2 'height will have format, but weight will not';
column sex age,(height weight);
define sex / group;
define age / across ;
define height / mean;
define weight / mean;
compute height;
call define(_col_,'style','style={background=ah.}');
endcomp;
run;
ods html close;

** With CALL DEFINE on only some across items;
ods html file='c:\temp\across4.html' style=sasweb;
proc report data=sashelp.class nowd;
title '4) With CALL DEFINE Format on _C#_';
column sex age,(height weight);
define sex / group;
define age / across ;
define height / mean;
define weight / mean;
compute height;
/* _c2_ is height/age=11 _c3_ is weight/age=11*/
call define('_c2_','style','style={background=ah.}');

/* _c4_ is height/age=12 */
call define('_c4_','style','style={background=ah.}');

/* _c6_ is height/age=13 */
call define('_c6_','style','style={background=ah.}');

/* do NOT apply format to height for ages 14 and 15 */
/* age=14 _c8_=height, _c9_=weight */
/* age=15 _c10_=height, _c11_=weight */

/* _c12_ is height/age=16 */
call define('_c12_','style','style={background=ah.}');
endcomp;
run;
ods html close;

** Change number of "across" ages.;
** "flatten file" with REPORT;
** could "macroize" the rename of the _c#_ names to the values;
** only show 1 variable for each age;

ods listing;

proc report data=sashelp.class nowd
out=work.repout(rename=(_c2_=ag11 _c3_=ag12 _c4_=ag13 _c5_=ag14));
where age le 14;
title '5a) select subset and create "flat" report output';
column sex age,height;
define sex / group;
define age / across ;
define height / mean;
rbreak after / summarize;
run;

proc print data=work.repout;
title '5b) What was created by PROC REPORT';
run;
ods listing close;

ods html file='c:\temp\across5b.html' style=sasweb;
proc report data=repout nowd missing;
title '5c) Flatten dataset and rename items';
title2 'Can "macroize" a solution like this';
column _break_ sex ag11 ag12 ag13 ag14;
define _break_ / group noprint;
define sex / group;
define ag11 / analysis style(column)={background=ah.};
define ag12 / analysis style(column)={background=ah.};
define ag13 / analysis style(column)={background=ah.};

** do not want trafficlighting for age 14, so do not use;
** style override for this report item;
define ag14 / analysis;
run;
ods html close;
[/pre]