ODS Excel- conditional sheet tab color

[Ronein]

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;

[Ksharp · Accepted solution]

 
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;

[Ronein]

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;

[Ksharp]

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.

[Ronein]

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;

[Ksharp]

Yes.