PROC REPORT: Conditional formatting based on value of ACROSS variable

[Resa]

We are trying to create a report using PROC REPORT in which we like to use conditional formatting based on the value of the across variable.

The environment we are working in for this report is SAS 9.1 and EG 4.1

The input dataset we are using has a structure similar to the this test dataset.

data work.testdataset ;
       length varA varB varC $2 ;
       length varD 8 ;
       input varA varB varC varD ;
       datalines ;
x1 y1 0x .
x1 y1 1x 3
x1 y1 2x 6
x1 y2 0x 2
x1 y2 1x 4
x1 y2 2x .
x2 y1 0x .
x2 y1 1x 1
x2 y1 2x 2
x2 y2 0x 4
x2 y2 1x 8
x2 y2 2x .
;
run ;

The base code for the report we are creating is as follows:

title ; footnote ;
proc report data=work.testdataset nowindows ;
       columns varA (varB,(varC,varD)) ;
       define varA / group ;
       define varB / across nozero ;
       define varC / across nozero ;
       define varD / analysis sum ;
run ;

With the output set to HTML this produces a report as shown in the attachment (PROC_REPORT_ACROSS_VARS.PNG).

Our wish is to use conditional formatting based on the content of varB.

In case the value of varB is y1 then the format of the column should be for example 8.2.

In case the value of varB is y2 then the format of the column should become 8.3.

I have read the suggestion made in the thread PROC REPORT: Conditional formatting of columns of across variable but the solution as mentioned in the paper is conditional formatting based on the content of the cells (so basically the value of varD).

Hopefully there is a more elegant solution than:

call define ('_cx_','format','8.x')

Since this would have to be edited each time new values of varC occur.

Any help or hints are appreciated.

[Cynthia_sas]

Hi:

   The _Cx_ syntax is elegant, when combined with macro processing. Before you run your PROC REPORT, you can run a macro to find the unique values of VARC -- then the macro program can be coded to generate the CALL DEFINE statements that you need. Yes, it is indirect...but it is how you deal with the absolute column numbers.

  This paper http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf  shows an example of a macro program which does that kind of processing. The relevant pages are 12-14, but the whole paper is worthwhile. The example in the paper uses some other condition to do the formatting. In your case, if you had something like this program, which is based on SASHELP.CLASS, I know that F values will occur first and M values will occur second. So I don't need to use a condition to set the color, I just make _C2_ (Females) have a background of pink and I make _C3_ a background of cyan.

  If you use the OUT= option, you can tell what absolute column names have been created. If you use the macro example or something like it, then you just need to have some idea of what all the possible values could be and what colors you would want to assign. You can code the colors in the macro program or use other techniques to set the colors.

cynthia

ods html file='c:\temp\color_gender.html' style=sasweb;

proc report data=sashelp.class nowd out=whatcols;

  column age sex,height;

  define age / group;

  define sex / across;

  define height / mean;

  compute sex;

       call define('_c2_','style','style={background=pink}');

       call define('_c3_','style','style={background=cyan}');

  endcomp;

run;

proc print data=whatcols;

run;

ods html close;

[Resa]

Cynthia,

Thanks for your reply. I have read the paper you mention since you also made a reference to this paper in the thread that I mention in my initial question.

The call define solution as you suggest when indeed you know or can deduct the columns that are to be set. In your example it is unlikely that another gender will occur besides the M and the F.

In our case the values of varC may vary and in that case the columns of which the formats are to be set will differ.

From your reply I understand that the only possibility is to use a macro before the PROC REPORT in order to determine the values for x in order to dynamically set the formats for the _cx_ column. But that there is no way to dynamically set the formats inside PROC REPORT since after the pre processing it is impossible to reference the original values of varB.

--Resa

[Cynthia_sas]

Hi,

In your last sentence did you mean the original values of varB or varC??

When you run your macro you can capture the varC values that will become _Cn_ absolute column numbers. So if i needed to know what _C2_ was (in my example, f

Or m ) i could capture it.

Cynthia

[bheinsius]

Using _Cx_ is a solution that requires a lot of code and SAS expertise, which feels like a last resort to me.

More elegant would be to obtain/reference the across variable values within proc report itself.

Is that at all possible?

-Bart

[Cynthia_sas]

Hi:

Being able to reference the ACROSS variable values directly is not as simple as it sounds. My earlier example had only 1 item(HEIGHT) under each value for SEX – so in essence, _C2_ is not just the column for Females, it is the column for the MEAN of HEIGHT of FEMALES; and _C3_ is the column for the MEAN of the HEIGHT of MALES.  And that’s just if I had 1 item under SEX…what if I had multiple items or a mix of items and statistics under each unique value of the ACROSS item. And, what if I had 2 ACROSS items, like

Column product year,month,(sales inventory);

In the program below, I have a PROC REPORT program that uses REGION (from SASHELP.SHOES) as an ACROSS item, and PRODUCT as the GROUP item on the report, I can write a macro in such as way that my macro program can capture the values that will be set. So, for example, if I run my macro the first time, using a WORK version of SASHELP.SHOES with 3 regions, then my macro can tell me this:

----> _c2_ is absolute column for Asia

----> style for this region is style={background=cx99FFCC}

----> _c3_ is absolute column for Canada

----> style for this region is style={background=cx9999FF}

----> _c4_ is absolute column for Pacific

----> style for this region is style={background=cxFF9999}

But, if I run the macro a second time, on a different dataset that has only 2 regions, then I can have my macro tell me this:

----> _c2_ is absolute column for United States

----> style for this region is style={background=cxFF99FF}

----> _c3_ is absolute column for Western Europe

----> style for this region is style={background=cxFF00FF}

So,  although my program sets the background for the whole column based on the user-defined format, you could easily also write an IF statement, since the program createsa macro variable that holds the value for each absolute column name.

It is more advanced PROC REPORT coding and if you really need the report to be dynamic, then macro coding is the only way to make that happen. If you know the entire universe of possible values for your ACROSS item(as I do for SASHELP.SHOES), then you can code the PROC FORMAT ahead of time.It is always possible to generate the PROC FORMAT dynamically, too, but I think this example goes far enough to show what is possible.

Cynthia

options mprint;

ods listing close;

** start macro program definition;

%MACRO do_acr(dsn=, oname=, startcol=2,

              varsunder=1, varsleft=1);

** get list of distinct regions from input data set;

** and get count of distinct regions;

proc sql;

  select distinct region, count(distinct region)

      into :reglist separated by '~', :num

  from &dsn;

quit;

%put &num &reglist;

** create macro variables based on region list;

** and use format to set color;

** macro variables will hold the actual region value;

** and the absolute column is calculated automatically.;

DATA _NULL_;

  length reglist $200 thisreg $25 thisbkg $20 stylestring $100;

  reglist="&reglist";

  varsleft = &varsleft;

  do i = 1 to #

      abscol = i + varsleft;

      thisreg = scan(reglist,i,'~');

      thisbkg = put(thisreg,$rfmt.);

      stylestring='style={background='||trim(thisbkg)||'}';

      call symput('_c'||trim(left(put(abscol,2.)))||'_',trim(thisreg));

      call symput('s'||trim(left(put(abscol,2.))),trim(stylestring));

  end;

run;

** write info about absolutecolumn and value to log;

%DO i=&startcol %TO %EVAL(&num*&varsunder+&varsleft)%BY &varsunder;

  %put ----> &i. is absolute column for&&&_c&i._ ;

  %put ----> style for this region is&&s&i;

%end;

** create HTML file and use oname macro variable;

ods html file="c:\temp\&oname..html"style=sasweb;

  title 'Regions should be different colors based on format';

proc report data=&dsn  nowd;

  column ('Product' product) region,sales;

  define product / group ' ';

  define region / across;

  define sales / mean ' ';

  compute sales;

    %DO i=&startcol %TO %EVAL(&num*&varsunder+&varsleft)%BY &varsunder;

       call define("_C%eval(&i)_",'style',"&&s&i");

    %END;

  endcomp;

run;

ods html close;

%mend do_acr;

** end macro program definition;

** create a user-defined format with the colors;

** desired for all POSSIBLE values of REGION;

proc format;

  value $rfmt 'Africa'='cx66ffff'

              'Asia'='cx99FFCC'

              'Canada'='cx9999FF'

              'Central America/Caribbean'='cxCCCC00'

              'Eastern Europe'='cxCCCCFF'

              'Middle East'='cxCCFFCC'

              'Pacific'='cxFF9999'

              'South America'='cxFFFF99'

              'United States'='cxFF99FF'

              'Western Europe'='cxFF00FF';

run;

** create a data set with 3 values for Region;

proc sort data=sashelp.shoes out=sortshoes1;

  by region;

  where region in ('Asia' 'Canada' 'Pacific');

run;

** invoke macro with 3 values for region;

%do_acr(dsn=sortshoes1, oname=ss1)

** now create a new data set with 2 values for Region;

proc sort data=sashelp.shoes out=sortshoes2;

  by region;

  where region in ('United States' 'Western Europe');

run;

** invoke macro with data that has 2 values and diff colors;

%do_acr(dsn=sortshoes2, oname=ss2)

** now create a data set with all vaues for Region;

proc sort data=sashelp.shoes out=sortshoes3;

  by region;

run;

** invoke macro with data that has all values and diff colors;

%do_acr(dsn=sortshoes3, oname=ss3)

title;

footnote;

ods listing;

options nomprint;

[Resa]

Cynthia,

Thanks for providing the example.

As Bart indicated earlier it would have been, IMHO, if it would have been possible to reference the across variable within the PROC REPORT.

I'll mark this thread as nevertheless as answered since a more elegant solution than a macro is not possible.

--Resa

[bheinsius]

Hi Cytnhia,

Cynthia@sas wrote:
...
Being able to reference the ACROSS variable values directly is not as simple as it sounds.
...

From a functional (user) point of view I see no problem coding the following:

proc report data=sashelp.class nowd out=whatcols;

  column age sex,height;

  define age / group;

  define sex / across;

  define height / mean;

  compute height;

    if (sex = 'F') then

      call define(_col_,'style','style={background=pink}');

    else

      call define(_col_,'style','style={background=cyan}');

  endcomp;

run;

That this does not work because proc report does not set the across column variable values in a compute block, is something else.

I believe this would be a valuable enhancement to proc report making it much easier to use in many cases.

-Bart

[Cynthia_sas]

Hi:

  You can always make a suggestion to add that capability via the SASware ballot process.

http://support.sas.com/community/ballot/

cynthia

[bheinsius]

How about SAS itself finding that a useful feature and adding it to the product?

I sometimes (often) miss SAS' drive for enhancing existing products.

[JW_Hyde]

Hello-have we seen any progress for this request from 5 years ago?

[Cynthia_sas]

Hi:
I do not believe the item was ever added as a SASWare ballot item for voting. You might want to check with Tech Support. As far as I know, the original discussion about how to use a Macro program to determine the ACROSS variable values is still a relevant discussion.

cynthia

[JW_Hyde]

Thank you Cynthia. I will look to add it to the Ballot. I am just looking for an option similar to the Excel Conditional Formatting colors.

[Cynthia_sas]

Hi: 

  I am not sure of what it is you want. SAS does NOT have anything like this Excel popup window for conditional highlighting:

...nor is it likely to add that feature to PROC REPORT since the conditional highlighting you do in REPORT, PRINT or TABULATE must be performed in code.

(also added that SAS Web Report Studio, part of the SAS BI Platform does have popup GUI windows to do trafficlighting or conditional highlighting.)

  This posting was about how to identify the ACROSS columns, which seems to me to be different than this popup window in Excel.

  Highlighting or Traffic lighting is currently possible with PROC PRINT, PROC REPORT and PROC TABULATE using user defined formats and there is an additional method in PROC REPORT using a COMPUTE block and the CALL DEFINE statement.

  Please see this paper http://support.sas.com/resources/papers/proceedings13/366-2013.pdf on pages 9/10 for how to do highlighting with code in PROC PRINT and then on pages 12/13 for PROC REPORT. Examples of highlighting with TABULATE are shown on pages 19/20, but not using user defined formats.

  Here's a TABULATE example with PROC FORMAT for the highlighting:

proc format;
value agef 11, 12, 13 = 'cxcccccc'
           14, 15, 16 = 'verylightblue';

value wtfmt 0-74 = 'lightblue'
            75-85 = 'lightyellow'
            86-99 = 'lightgreen'
			100-high = 'lightred'
            . = 'pink';
run;

ods html file='c:\temp\forum_tab_highlight.html';
 proc tabulate data=sashelp.class f=7.2;
 title 'Proc Tabulate with highlighting';
 var height weight ;
 class age sex;
 classlev age /style={background=agef.};
 table age=' '  all='Total',
       mean=' '*sex='Gender Avg'*(height weight*{s={background=wtfmt.}})
       mean='Overall Avg'*(height weight*{s={background=wtfmt.}}) /
       style_precedence=row box={label='Age' s={vjust=b}};
 run;
ods _all_ close;

What procedure are you using and what ODS destination are you interested in. The paper only shows HTML output, but trafficlighting or highlighting is supported by any destination that supports style overrides. There are other methods of performing traffic lighting as well, but some of them (changing the table template or designing a custom table template) are really outside the scope of what was covered in the paper.

cynthia

[JW_Hyde]

Thanks Cynthia. This paper points me in the right direction.