BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KonstantinVasil
Obsidian | Level 7

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
KonstantinVasil
Obsidian | Level 7

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; 

 

 

View solution in original post

7 REPLIES 7
Oligolas
Barite | Level 11

_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;
________________________

- Cheers -

KonstantinVasil
Obsidian | Level 7

 

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.

Oligolas
Barite | Level 11

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

 

 

________________________

- Cheers -

Oligolas
Barite | Level 11

screenshot_15.jpg

________________________

- Cheers -

KonstantinVasil
Obsidian | Level 7

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;
Oligolas
Barite | Level 11

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

________________________

- Cheers -

KonstantinVasil
Obsidian | Level 7

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; 

 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2709 views
  • 5 likes
  • 2 in conversation