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;
%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;
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.
%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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.