using proc report to make excel heatmap

[kalbo]

Hi, I am creating a heat map in excel based on the "have" data set.

I would like to shade the "run" cells for each trial red if there is no "run" value for a trial. And shade cells green if there is at least one value for "run" within each trial.

So based on the data below, "trial" 002 would have all "run" values shaded red in excel.

All other trials would have green "run" values in excel.

Thanks.

%let outfile=; /*add excel path here*/
filename _xlsx_ "&outfile.";

data have;
input trial : $3. environment : $4. run $1.;
datalines;
001 PROD Y
001 TEST        
002 TEST
002 PROD  
003 PROD Y
004 TEST Y
004 PROD Y
;
/*ODS excel start*/
ods excel file=_xlsx_
		  style=statistical;
		  
ods excel options(frozen_headers="1"
    			  autofilter="all"
    			  flow="table"
    			  sheet_name="Blueprint");

proc report data=have nowd;
	columns trial environment run;
	define trial / order;
	define environment / display;
	define run / display;
run;

/*ODS excel end*/
ods excel close;

[PaigeMiller]

I would like to shade the "run" cells for each trial red if there is no "run" value for a trial. And shade cells green if there is at least one value for "run" within each trial.

I don't think this can be done directly in PROC REPORT, because PROC REPORT (as far as I know) doesn't know about the other values that appear in the trial on other rows of the data set. Now, if you create a new variable in a DATA step which is numeric and has value 0 when run is missing and a value of 1 when run is Y, I think you could do this in PROC REPORT, using the sum of these 0/1 values, if the sum is zero then red and if the sum is greater than 0 then green.

[Ksharp · Accepted solution]

%let outfile=c:\temp\temp.xlsx ; /*add excel path here*/
filename _xlsx_ "&outfile.";

data have;
input trial : $3. environment : $4. run $1.;
datalines;
001 PROD Y
001 TEST        
002 TEST
002 PROD  
003 PROD Y
004 TEST Y
004 PROD Y
;

proc sql;
create table temp as
select *,case when n(run) then 'green' else 'red ' end as color
 from have 
  group by trial
    order by 1,2;
quit;
/*ODS excel start*/
ods excel file=_xlsx_
    style=statistical;
    
ods excel options(frozen_headers="1"
         autofilter="all"
         flow="table"
         sheet_name="Blueprint");

proc report data=temp nowd;
 columns trial environment run color;
 define trial / order;
 define environment / display;
 define run / display;
 define color /noprint;
 compute color;
      call define(_row_,'style',cats('style={background=',color,'}'));
 endcomp;
run;

/*ODS excel end*/
ods excel close;