topic Re: ODS Excel- conditional sheet tab color in SAS Programming

ODS Excel- conditional sheet tab color

Ronein — Thu, 15 May 2025 08:50:42 GMT

Hello

I am using ODS excel to export report into excel.

I want to color the sheet tab name based on a macro variable:

if there is an invoice>30,000 then sheet tab color be red, else be green.

So, as I understand need to define proc format that get color value based on the macro var.

What is the way to do it please???


 
proc sql  noprint;
select max(case when invoice>30000 then 1 else 0 end ) as Ind_max_invoice_Gt_30K  into :Ind_max_invoice_Gt_30K
from sashelp.cars
where Make="Honda"
;
quit;

ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/Honda_Report.xlsx" 
options  (sheet_name='Honda'
embedded_titles='yes' 
embedded_footnotes='yes'
sheet_interval="proc" 
gridlines = 'yes'
zoom='50'
tab_color=&Color_Fmt.  /*Change TAB Color based on macro var  Ind_max_invoice_Gt_30K  :  if =1 then Red , else Green*/
);
title 'Honda_Report';
proc report data=sashelp.cars contents="Honda Cars";
	  where Make="Honda";
	  column ("Car" make model) ("Cost" msrp invoice) 
           ("Fuel Efficiency" mpg_city mpg_highway);  
run;
ods excel  close;

Re: ODS Excel- conditional sheet tab color

Ksharp — Thu, 15 May 2025 09:43:24 GMT

 
proc sql  noprint;
select case when max(invoice)>30000 then 'red ' else 'green' end  as Ind_max_invoice_Gt_30K  into :Ind_max_invoice_Gt_30K
from sashelp.cars
where Make="Honda"
;
quit;


ods excel file="c:\temp\Honda_Report.xlsx" 
options  (sheet_name='Honda'
embedded_titles='yes' 
embedded_footnotes='yes'
sheet_interval="proc" 
gridlines = 'yes'
zoom='50'
tab_color="&Ind_max_invoice_Gt_30K."  /*Change TAB Color based on macro var  Ind_max_invoice_Gt_30K  :  if =1 then Red , else Green*/
);
title 'Honda_Report';
proc report data=sashelp.cars contents="Honda Cars" nowd;
	  where Make="Honda";
	  column ("Car" make model) ("Cost" msrp invoice) 
           ("Fuel Efficiency" mpg_city mpg_highway);  
run;
ods excel  close;

Re: ODS Excel- conditional sheet tab color

Ronein — Thu, 15 May 2025 10:31:50 GMT

It is perfect,

only one note--- the first query is not 100% correct by my opinion.

Need to check first if there is any row with value greater then 30,000 and then based on this value convert it to red or green.

In your code I think that it check every row and in bottom line the color based on last row only


  
proc sql  noprint;
select case when a.max_invoice>30000 then 'red ' else 'green' end  as Ind_max_invoice_Gt_30K  into :Ind_max_invoice_Gt_30K
From 
(select max(invoice) as max_invoice
from sashelp.cars
where Make="Honda") as a
;
quit;
%put Ind_max_invoice_Gt_30K=&Ind_max_invoice_Gt_30K;

 
ods excel file="c:\temp\Honda_Report.xlsx" 
options  (sheet_name='Honda'
embedded_titles='yes' 
embedded_footnotes='yes'
sheet_interval="proc" 
gridlines = 'yes'
zoom='50'
tab_color="&Ind_max_invoice_Gt_30K."  /*Change TAB Color based on macro var  Ind_max_invoice_Gt_30K  :  if =1 then Red , else Green*/
);
title 'Honda_Report';
proc report data=sashelp.cars contents="Honda Cars" nowd;
	  where Make="Honda";
	  column ("Car" make model) ("Cost" msrp invoice) 
           ("Fuel Efficiency" mpg_city mpg_highway);  
run;
ods excel  close;

Re: ODS Excel- conditional sheet tab color

Ksharp — Fri, 16 May 2025 01:24:27 GMT

Nope.
Here I used summary function "max(invoice)" which is applied to all the data ,not just based on the last row , it is based on ALL the rows.

a.k.a max() would get only ONE value ,not N values for all the rows.

If you don't believe it, you could test it on your own.

Re: ODS Excel- conditional sheet tab color

Ronein — Fri, 16 May 2025 10:15:02 GMT

great, thank you .

So this code-

proc sql  noprint;
select case when a.max_invoice>30000 then 'red ' else 'green' end  as Ind_max_invoice_Gt_30K  into :Ind_max_invoice_Gt_30K
From 
(select max(invoice) as max_invoice
from sashelp.cars
where Make="Honda") as a
;
quit;

is equivalent to this code-

proc sql  noprint;
select case when max(invoice)>30000 then 'red ' else 'green' end  as Ind_max_invoice_Gt_30K  into :Ind_max_invoice_Gt_30K
from sashelp.cars
where Make="Honda"
;
quit;

Re: ODS Excel- conditional sheet tab color

Ksharp — Sat, 17 May 2025 01:24:13 GMT

Yes.