BookmarkSubscribeRSS Feed
Ronein
Onyx | Level 15

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;
3 REPLIES 3
Ksharp
Super User
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;

Ksharp_0-1746595525646.png

 

ballardw
Super User

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;
Cynthia_sas
Diamond | Level 26

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

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
  • 3 replies
  • 566 views
  • 0 likes
  • 4 in conversation