How do I add conditional formatting to individual cells

[abcrandy]

I'm new to SAS but was able to create a report. I've included a small screenshot below. The issue that I have is with the conditional formatting for cell background color. I'd like to change the background color to grey for any cell that does not contain a value. As you can see from the screenshot, I was able to accomplish this for Mean and StdDev but failed to attain success with the Pax LF field. I'm including my code as well and any help would be much appreciated.

	col	
	(Region_Cat Orig_Stn_Cd Dest_stn_cd CgCpFc_AcTp_Cd ('Last 30 Days' Mean_Kg_Cpty StdDev_Cap_kgs) Schd_dprt_gdt,( Pax_Lf  Capacity_kg))	;
		define Region_Cat / "Region" group center style(column)=[cellwidth=1in];
		define Orig_Stn_Cd / "Orig" group center style(column)=[cellwidth=.45in];
		define Dest_stn_cd / "Dest" group center style(column)=[cellwidth=.45in];
		define CgCpFc_AcTp_Cd / "Fleet" group center style(column)=[cellwidth=0.45in] ;
		define Mean_Kg_Cpty / "Mean (kg)" group format=comma7. center style(column)=[cellwidth=.45in];
		define StdDev_Cap_kgs / "StdDev (kg)" group format=comma7. center style(column)=[cellwidth=0.45in] ;	
		define Schd_dprt_gdt / " " across center style(header)=[borderleftwidth=2 borderleftcolor=#C01933 /*cellwidth=1in*/ borderrightwidth=2 borderrightcolor=#C01933];
		define  Pax_Lf / analysis mean "Pax LF"  format=Percent10. center style(column)=[borderleftwidth=2 borderleftcolor=#C01933] center style(column)=[borderleftwidth=2 borderleftcolor=#C01933] center style(header)=[borderleftwidth=2 borderleftcolor=#C01933];
		define Capacity_kg / analysis mean "Capacity (kg)" format=comma7. center style(header)=[borderrightwidth=2 borderrightcolor=#C01933] style(column)=[borderrightwidth=2 borderrightcolor=#C01933];

	compute Orig_Stn_Cd;
			if (Orig_Stn_Cd NE '') then tmp_orig =Orig_Stn_Cd;
			if (Orig_Stn_Cd EQ '') then Orig_Stn_Cd=tmp_orig;
	endcomp;

	compute Dest_stn_cd;
			if (Dest_stn_cd NE '') then tmp_dest =Dest_stn_cd;
			if (Dest_stn_cd EQ '') then Dest_stn_cd=tmp_dest;
	endcomp;

	compute CgCpFc_AcTp_Cd;
			if (CgCpFc_AcTp_Cd NE '') then tmp_AC =CgCpFc_AcTp_Cd;
			if (CgCpFc_AcTp_Cd EQ '') then CgCpFc_AcTp_Cd=tmp_AC;
	endcomp;

	compute StdDev_Cap_kgs;
		if (StdDev_Cap_kgs=.) then 
			call define (_col_,"style", "style={background=grey}") ;
	endcomp;

	compute Mean_Kg_Cpty;
		if (Mean_Kg_Cpty=.) then 
			call define (_col_,"style", "style={background=grey}") ;
	endcomp;

	compute Pax_Lf;
		if (compress(Pax_Lf)=.) then 
			call define (_col_,"style", "style={background=grey}") ;
	endcomp;

[Cynthia_sas]

HI:
Pax_Lf and Capacity_kg are both "under" an ACROSS variable -- that means if you want to "touch" them in a COMPUTE block, you have to know their absolute column number. Please start on page 7 http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf and keep reading until you find the Display 5 pix that shows the _C2_, _C3_ examples. Then keep reading to see how you use the absolute column numbers in a COMPUTE block.

In your call define statement, I am sort of surprised to see the COMPRESS function being used for a numeric variable? I would expect to see this NOTE in the log:
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

So I'm not exactly sure that is the right test to be doing.

cynthia

[abcrandy]

Thanks for the reply Cynthia_Sas. I'm a little confused by the article. How do you find the absolute column number ? That part wasn't clear to me.

As far as the compress in the define function, I was trying different solutions and wanted to see if it was reading the Pax_Lf value as a number or char. The compress is removed in my current logic.

[abcrandy]

Should my compute for Pax_LF look like the code below?

compute Pax_Lf;

if (Pax_Lf=.) then

call define (_c7_,"style", "style={background=grey}") ;

endcomp;

[Cynthia_sas · Accepted solution]

Hi:

You can have the variable name on the COMPUTE statement, but you have to refer to _c7_ (or whatever the number is) in the IF condition too.

 
In the call define statement, you have to quote the variable number: '_c7_'.

To determine the column numbers, you either eyeball it and count it yourself, or you do an OUT= on the PROC REPORT step. For example, consider this code and output:

Note how the PROC PRINT shows the absolute numbers only for the variables that were under the ACROSS item.

Then, once you know what the absolute numbers are, you can then write your COMPUTE block to do the highlighting:

Hope this helps,

Cynthia

cynthia

[abcrandy]

Thanks, this worked. Your awesome Cynthia_sas