topic PROC REPORT-Color cells in specific column based on values in other column in SAS Programming

PROC REPORT-Color cells in specific column based on values in other column

Ronein — Wed, 07 May 2025 04:51:32 GMT

Hello

I want to color cells in specific column based on values in other column.

I have tried this code but in output result i dont see colors

 
data LONG1;                                                                                                                            
Input WD EXPD RCVD RCVD_ALL _NEWC_  $;                                                                                                                 
cards;    
-1 49 49 56 Y
01 26 26 77 Y
02 77 45 0 N
03 71 0 0 N
04 44 0 0 N
;
RUN;



proc report data=LONG1 missing nowd;
	column  (WD, ( EXPD RCVD RCVD_ALL));
	define WD / 'Working Day' across  /*NOZERO*/ ORDER=DATA;
	define EXPD / analysis 'Expd' missing;
	define RCVD / analysis 'Rcvd' missing;
	define RCVD_ALL / analysis 'Rcvd all' missing;

	compute RCVD;
		if _NEWC_ = "Y" then
			do;
				call define (_col_,"style", "style={background=green}");
			end;
		else if _NEWC_ = "N" then
			do;
				call define (_col_,"style", "style={background=red}");
			end;
	endcomp;
run;
quit;

Re: PROC REPORT-Color cells in specific column based on values in other column

Ksharp — Wed, 07 May 2025 05:25:32 GMT

data LONG1;                                                                                                                            
Input WD EXPD RCVD RCVD_ALL _NEWC_  ;                                                                                                                 
cards;    
-1 49 49 56 1
01 26 26 77 1
02 77 45 0 0
03 71 0 0 0
04 44 0 0 0
;
RUN;



proc report data=LONG1 missing nowd out=out ;
	column  (WD, ( EXPD RCVD RCVD_ALL  _NEWC_  )) ;
	define WD / 'Working Day' across  /*NOZERO*/ ORDER=DATA;
	define EXPD / analysis 'Expd' missing;
	define RCVD / analysis 'Rcvd' missing;
	define RCVD_ALL / analysis 'Rcvd all' missing;
    define _NEWC_/analysis noprint;

	compute _NEWC_;
		if _c4_ = 1 then
			do;
				call define ('_c2_',"style", "style={background=green}");
			end;
		else if _c4_= 0 then
			do;
				call define ('_c2_',"style", "style={background=red}");
			end;


		if _c8_ = 1 then
			do;
				call define ('_c6_',"style", "style={background=green}");
			end;
		else if _c8_= 0 then
			do;
				call define ('_c6_',"style", "style={background=red}");
			end;


		if _c12_ = 1 then
			do;
				call define ('_c10_',"style", "style={background=green}");
			end;
		else if _c12_= 0 then
			do;
				call define ('_c10_',"style", "style={background=red}");
			end;


		if _c16_ = 1 then
			do;
				call define ('_c14_',"style", "style={background=green}");
			end;
		else if _c16_= 0 then
			do;
				call define ('_c14_',"style", "style={background=red}");
			end;


	  if _c20_ = 1 then
			do;
				call define ('_c18_',"style", "style={background=green}");
			end;
		else if _c20_= 0 then
			do;
				call define ('_c18_',"style", "style={background=red}");
			end;
	endcomp;
run;

Re: PROC REPORT-Color cells in specific column based on values in other column

ballardw — Wed, 07 May 2025 10:11:14 GMT

Proc Report builds reports from left to right. So basically any condition cannot see values of a column that is to the right as they have not been populated yet. And you have generally have to specify the variables on the COLUMNS statement, i.e. _NEWC_ , so the variable order can be determined.

You can specify NOPRINT on a Define so a variable does not appear in the report but the values can be used, such as the following. Adding _Newc_ in the first column with Noprint means its value is available for reference by any other column.

proc report data=LONG1 missing nowd;
	column _NEWC_ (WD, ( EXPD RCVD RCVD_ALL));
   define _newc_ / noprint;
	define WD / 'Working Day' across  /*NOZERO*/ ORDER=DATA;
	define EXPD / analysis 'Expd' missing;
	define RCVD / analysis 'Rcvd' missing;
	define RCVD_ALL / analysis 'Rcvd all' missing;

	compute RCVD;
		if _NEWC_ = "Y" then
			do;
				call define (_col_,"style", "style={background=green}");
			end;
		else if _NEWC_ = "N" then
			do;
				call define (_col_,"style", "style={background=red}");
			end;
	endcomp;
run;
quit;

Re: PROC REPORT-Color cells in specific column based on values in other column

Cynthia_sas — Wed, 07 May 2025 22:15:23 GMT

Hi:

In order to test the "_NEWC_" variable inside your PROC REPORT code, it MUST be on the COLUMN statement for PROC REPORT. Even if you have the variable in your data, PROC REPORT has to know about the variable before you can use it in an IF statement in a COMPUTE block. Then, as indicated, the placement of the variable on the COLUMN statement makes a difference. So if you are going to test the "_NEWC_" variable in the COMPUTE block for RCVD then it has to appear BEFORE RCVD in the COLUMN statement. There are other methods that may work too, but they are more complex.

Cynthia