topic Re: Proc report - above, below and at diagonal color in SAS Programming

Proc report - above, below and at diagonal color

KonstantinVasil — Wed, 20 Feb 2019 10:54:23 GMT

I have found certain examples for more complicated cases of color coding but could not reproduce the result for a simpler case.

I have a NxN table and I want the cells to have different color depending if they are above, below or at the diagonal.

I have been trying with solution of the type:

proc report data=have;
	column col1 col2;
		COMPUTE before;
			IF _row_ / _col_ = 1 THEN DO;
				CALL DEFINE(_col_,'style','style={background=atDiag}');
			END;
			IF _row_ / _col_ > 1 THEN DO;
				CALL DEFINE(_col_,'style','style={background=beDiag}');
			END;
			IF _row_ / _col_ < 1 THEN DO;
				CALL DEFINE(_col_,'style','style={background=abDiag}');
			END;
		ENDCOMP;
run;

However even a simpler cell coloring doesn't work:

proc report data=sashelp.cars;
	column model weight;
		COMPUTE before;
			IF _row_ = 2 and _col_ = 2 THEN DO;
				CALL DEFINE(_col_,'style','style={background=yellow}');
			END;
		ENDCOMP;
run;

Is there a simple way to achieve this without using additional numbers and just the row and col number?

Re: Proc report - above, below and at diagonal color

Oligolas — Thu, 21 Feb 2019 07:55:44 GMT

_row_ and _col_ are only known by the call define Statement(link) you can not perform a Logical Operation on them.

this way it works:

proc report data=sashelp.cars;
   where index(model,'A4');
	column model weight;
		COMPUTE weight;
         count+1;
			IF count = 2 THEN DO;
				CALL DEFINE(_col_,'style','style={background=yellow}');
			END;
		ENDCOMP;
run;

Re: Proc report - above, below and at diagonal color

KonstantinVasil — Thu, 21 Feb 2019 09:02:36 GMT

Thank you but with this solution I can only color along the column <weight>. I would like to color across the whole table and every column and row with three different colors for above, below or at the diagonal.

Re: Proc report - above, below and at diagonal color

Oligolas — Fri, 22 Feb 2019 06:40:32 GMT

Then apply any logic for any row or column as presented in the sample and it will work

Re: Proc report - above, below and at diagonal color

Oligolas — Fri, 22 Feb 2019 06:50:29 GMT

Re: Proc report - above, below and at diagonal color

KonstantinVasil — Fri, 22 Feb 2019 09:04:25 GMT

Isn't there a simple way to specify COMPUTE for each column? Instead of having repeating compute statements or writing out all columns.

For example, I tried making a row_number variable as an order variable and tried to execute COMPUTE after row_number; expecting to conduct the computation after each row and for all columns - but it didnt work.

data temp; set sashelp.cars; row_n = _N_; if _N_ <12; run;

proc report data=temp NOWD ;
	column row_n make length;
	DEFINE row_n / '' order  ORDER=data;
 	DEFINE make / ''  order ORDER=data;
	COMPUTE after row_n;  /*only works if the compute is on make*/
		IF row_n = 4 and _col_ = 2 then CALL DEFINE(_col_,'style','style={background=yellow}');
	ENDCOMP;
run;

Re: Proc report - above, below and at diagonal color

Oligolas — Mon, 25 Feb 2019 12:48:08 GMT

Hi,

for different highlight logic you need distinct compute statements.

If there is no change in the highlight logic accross the columns you can highlight the whole _row_:

data temp; set sashelp.cars; row_n = _N_; if _N_ <12; run;

proc report data=temp NOWD ;
	column row_n make length;
	DEFINE row_n / '' order ORDER=data;
 	DEFINE make  / '' order ORDER=data;
	COMPUTE row_n;  
		IF row_n eq 4 then CALL DEFINE(_row_,'style','style={background=yellow}');
	ENDCOMP;
run;

If this doesn't answer your question maybe you could post an example of what you are trying to reach

Re: Proc report - above, below and at diagonal color

KonstantinVasil — Mon, 25 Feb 2019 14:32:53 GMT

Thank you for the help.

I have found a solution using a macro that iterates through all variables which columns should be colored. Then in creates a compute block for each variable. The coloring is also specific depending the variable order, in order to color depending on the location relative to the diagonal.

%macro color_migration(var_list);
	%do i=1 %to %sysfunc(countw(&var_list.));
		%let var = %scan(&var_list., &i.);	
		COMPUTE  &var.;
			IF order_var=&i. THEN CALL DEFINE(_col_,'style','style={background=cxFDE9D9}');
			else if order_var>&i. and order_var <28  THEN CALL DEFINE(_col_,'style','style={background=cxD8E4BC}');
			else if order_var <&i. and order_var >0 THEN CALL DEFINE(_col_,'style','style={background=cxE6B8B7}');
		ENDCOMP;
	%end;
%mend color_migration;

	proc report data= temp ;
	  column ( "Test"  order_var  Vars: );
	  define order_var / noprint order order=data ;
	  %color_migration(var_list=&var_list.);
	run;