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

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.

Sas Format_bkg.JPG

	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;
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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:

use_OUT_PROC_REPORT.png

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:

final_highlighting.png

Hope this helps,

Cynthia

cynthia

View solution in original post

5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
Fluorite | Level 6

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
Fluorite | Level 6

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
SAS Super FREQ

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:

use_OUT_PROC_REPORT.png

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:

final_highlighting.png

Hope this helps,

Cynthia

cynthia

abcrandy
Fluorite | Level 6
Thanks, this worked. Your awesome Cynthia_sas

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1098 views
  • 1 like
  • 2 in conversation