topic PROC REPORT: Conditional formatting based on value of ACROSS variable in ODS and Base Reporting

PROC REPORT: Conditional formatting based on value of ACROSS variable

Resa — Fri, 20 Jan 2012 20:45:55 GMT

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.

PROC REPORT: Conditional formatting based on value of ACROSS variable

Cynthia_sas — Fri, 20 Jan 2012 23:45:56 GMT

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;

PROC REPORT: Conditional formatting based on value of ACROSS variable

Resa — Sat, 21 Jan 2012 13:12:19 GMT

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

PROC REPORT: Conditional formatting based on value of ACROSS variable

Cynthia_sas — Sat, 21 Jan 2012 16:24:40 GMT

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

PROC REPORT: Conditional formatting based on value of ACROSS variable

bheinsius — Sat, 21 Jan 2012 17:40:36 GMT

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

PROC REPORT: Conditional formatting based on value of ACROSS variable

Cynthia_sas — Sun, 22 Jan 2012 04:05:49 GMT

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 &num;

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;

PROC REPORT: Conditional formatting based on value of ACROSS variable

Resa — Tue, 24 Jan 2012 10:36:37 GMT

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

PROC REPORT: Conditional formatting based on value of ACROSS variable

bheinsius — Wed, 25 Jan 2012 12:18:09 GMT

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

PROC REPORT: Conditional formatting based on value of ACROSS variable

Cynthia_sas — Thu, 26 Jan 2012 20:02:46 GMT

Hi:

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

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

cynthia

PROC REPORT: Conditional formatting based on value of ACROSS variable

bheinsius — Sat, 28 Jan 2012 22:16:18 GMT

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.

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

JW_Hyde — Fri, 14 Apr 2017 18:41:10 GMT

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

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

Cynthia_sas — Fri, 14 Apr 2017 20:46:58 GMT

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

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

JW_Hyde — Fri, 14 Apr 2017 20:50:01 GMT

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.

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

Cynthia_sas — Fri, 14 Apr 2017 22:37:52 GMT

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

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

JW_Hyde — Sat, 15 Apr 2017 11:24:01 GMT

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

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

JordiGC — Tue, 30 May 2023 13:29:26 GMT

Hello:

Is there any way to reference a cell to the right or to the left of the present value? I'm trying to compare the value of a cell with the value of the cell to the left of it, but I haven't been able to make it work in an elegant way. I have the following code:

	proc report data= ControlDiario showall out=columnas;
		where Central = "&PowerPlant";
		columns ('VLE' '' 'Superaciones' Dia) Grupo, ModoFuncionamiento, Medida, VLE, 
			Superaciones, (VLEComparacion Valor);
		define Dia / group order=internal 
			style(column)={width=160 tagattr='type:DateTime format:DD/MM/YYYY'};
		define Grupo / across '' format=&FormatoGrupos preloadfmt order=data;
		define ModoFuncionamiento / across nozero '';
		define Medida / across '' format=$FormatoMedida. preloadfmt order=data;
		define Superaciones / across '';
		define VLE / across '';
		define VLEComparacion / noprint;
		compute Valor;
			call symputx('ColValor', cats('_c', _col_, '_'));
			call symputx('ColVLE', cats('_c', _col_ - 1, '_'));
			if symget('ColValor') > symget('ColVLE') then do;
				call define(_col_, 'style', 'style=[background=very_light_vivid_red]');
			end;
		endcomp;

I thought ColValor would get the number of the present column (_col_) and I could compare it with the column to its left (ColVLE with value _col_ - 1), but the macro variables are not getting the right values.

On the other side, this is working, but I have to create a big array every time:

	proc report data = ControlMensual;
		columns ("VLE" '' 'Superaciones' Mes) Grupo, ModoFuncionamiento, Medida, 
			VLE, Superaciones, (VLEComparacion Valor);
		define Mes / group format=FormatoMeses. preloadfmt order=data;
		define Grupo / across '' format=&FormatoGrupos preloadfmt order=data;
		define ModoFuncionamiento / across nozero '';
		define Medida / across '' format=$FormatoMedida. preloadfmt order=data;
		define Superaciones / across '';
		define VLE / across '';
		define VLEComparacion / noprint;
		compute Valor;
			array Columnas(2000) 
			%do i = 1 %to 2000; _c&i._ %end;;
			if Columnas(_col_) > Columnas(_col_ - 1) then 
				call define(_col_, 'style', 'style=[background=very_light_vivid_red]');
		endcomp;
	run;

Do you know any way to resolve the name of the macros in the first code so that I can compare the present column with another one to the left of it?

Thanks and regards,

Jordi

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

Cynthia_sas — Wed, 31 May 2023 16:03:44 GMT

Hi: You have added a new post to a Forum thread that started in 2012 and was modified in 2017. The answer is still the same as originally posted. I do not think you can use CALL SYMPUTX and then SYMGET in the same COMPUTE block, but that would be a question for SAS Tech Support. Your second attempt with an ARRAY statement and a %DO loop doesn't make sense to me. I would expect that you're getting some kind of error in the COMPUTE block, something like:
ERROR: The %DO statement is not valid in open code.
WARNING: Apparent symbolic reference I not resolved.
ERROR: The %END statement is not valid in open code.

Because the %DO loop belongs in a SAS macro program. And the other issue I see is that you have at least 5 nestings of ACROSS items with 2 variables under each nesting. Then you make one of the variables NOPRINT -- but even as a NOPRINT item, your VLEComparacion variable will be assigned an absolute column number and so will the Valor variable. Even when you make a variable NOPRINT, it will get an absolute column number. So I think your second attempt will not work either. You might want to work with SAS Tech Support so you can show them ALL your data (or a subset) and ALL your formats and ALL your code and work with them on the best suggestion for how to do what you want to do. Also, I would suggest starting a new post in the forum. While your example does use ACROSS variables, your usage is so different from the original 2012 posting, that it really deserves to be a new post.
Cynthia

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

ballardw — Wed, 31 May 2023 15:51:19 GMT

Three suggestions:

1) Start your own thread. If you want to reference this thread you can copy the URL from your browser and paste into the text of your problem description. One thing as author of a thread you can select a response as a solution, assuming one is provided.

2) Provide a small data set in the form of data step OR reference one of the SAS supplied data sets in the SASHELP library that provides enough variables to do something similar to what you want.

3) With that example data set provide a table, such as manually made in a word processor, showing the behaviors you want.

Very complex comparisons might be better done before Proc Report (or other report procedure) hence the requests for data and desired appearance.

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

JordiGC — Thu, 01 Jun 2023 07:50:41 GMT

Thanks you for your answer, even if the thread has some years now.
About the second solution, it actually works :). I just need to make it more efficient.

Re: PROC REPORT: Conditional formatting based on value of ACROSS variable

JordiGC — Thu, 01 Jun 2023 07:51:35 GMT

Thanks for your suggestions ballardw. I will do so next time. There is no need to open a new thread for this topic now.