topic Re: proc report - conditional formatting based on group value in SAS Procedures

proc report - conditional formatting based on group value

js5 — Thu, 17 Oct 2019 14:26:05 GMT

Dear community,

I am trying to conditionally format my report based on the group value. Unfortunately, the formatting only gets applied to the first cell/row in the group, the one in which group value is displayed:

proc report data=sashelp.cars;
	columns Origin Make Model;
	define Origin / group;
	define Make -- Model / display;

	compute Model;

		if Origin EQ "Asia" then
			call define (_col_,"style","style={background=red}");
	endcomp;
quit;

Is there a way of applying a conditional format to all elements in a particular group? Thank you in advance!

Re: proc report - conditional formatting based on group value

ballardw — Thu, 17 Oct 2019 14:53:10 GMT

You may be having issues because of your code. Note that you will get this note in the Log:

NOTE: Groups are not created because the usage of Make is DISPLAY. To avoid this note, change all
GROUP variables to ORDER variables.

So the "group" is not created and your attempt to apply a style based on the "group" can't happen the way you want.

Here is one way by providing a non-printed variable that would exist on all the display rows.

data work.cars;
   set sashelp.cars;
   neworigin=origin;
run;

proc report data=work.cars;
	columns Origin neworigin Make Model;
	define Origin / Order;
   define neworigin/noprint;
	define Make -- Model / display;

	compute Model;

		if newOrigin EQ "Asia" then
			call define ('model',"style/replace","style={background=red}");
	endcomp;
quit;

Re: proc report - conditional formatting based on group value

Cynthia_sas — Thu, 17 Oct 2019 17:03:42 GMT

Hi:

You can also do this directly in PROC REPORT by using a temporary variable. This saves you the overhead of making a copy of the report data:

proc report data=sashelp.cars;
columns Origin Make Model;
define Origin / group;
define Make / display;
define Model / display;
compute before origin;
    length hold_or $6;
    hold_or=origin;
endcomp;
compute Model;
    if hold_or EQ "Asia" then
       call define (_col_,"style","style={background=red}");
endcomp;
run;

In the above instance the HOLD_OR item is a temporary item that is created at the beginning of each group on the first row, when Origin has a value. Temporary items in PROC REPORT are automatically retained (do NOT put the temporary item in the COLUMN statement or you will lose this benefit). That means HOLD_OR is available to be tested (instead of Origin) in the COMPUTE block for Model.

Hope this helps,

Cynthia

Re: proc report - conditional formatting based on group value

js5 — Fri, 18 Oct 2019 08:00:32 GMT

Thank you both! I tried @Cynthia_sas's solution and it worked perfectly.