topic Re: How do I highlight specific cells of summary data for excel output? Character and Numeric! in SAS Programming

How do I highlight specific cells of summary data for excel output? Character and Numeric!

mreynaud — Mon, 27 Mar 2023 19:17:20 GMT

Hi SAS community,

I'm creating an automated monthly report and my customer recently reached out and wanted to highlight specific cells of data in the summary sheet.

For this example, I am using sashelp.cars as my dataset. But here is a sample of what my customer is looking for:

They want to highlight the "Is this car an Audi?" record if it equals "Yes" AND if the "Is this car an Audi?" equals "Yes" then highlight the "Count" record as well.

I attempted to follow samples from this forum but I had a hard time adapting it since my columns are character and numerical.

Here is some sample code that will generate the summary tables from sashelp.cars and excel output. It would be so helpful if you can adapt it to highlight specific records!

/*Flag Car Type*/
data car_type;
set sashelp.cars;
if make="Audi" then is_audi="Yes";
run;

data car_type_2;
set car_type;
if is_audi=" " then is_audi="No";
run;

/*Create Summary Table*/
proc freq data=car_type_2; 
table is_audi/ out=audi_freq;
run;

/*Create Grand Total row*/
proc means noprint data=audi_freq;
  output out=summary sum=;
run;

data car_total;
  set audi_freq summary (in=in2);
  if in2 then Grand_Total='Grand Total:';
run;

/*Build Excel report*/

ODS listing close;
ODS excel FILE=&resultfile STYLE=htmlblue
options(embedded_titles='yes' embedded_footnotes='yes' zoom='100' sheet_interval='none' sheet_name='Summary' suppress_bylines='yes'  
orientation='landscape' fittopage="no" Pages_FitWidth = '2' Pages_FitHeight = '4' autofilter='yes' 	row_repeat='header'
 embedded_footnotes='yes');
 
proc report data=car_total split='@';

TITLE "Car Summary";

COLUMN (Grand_Total is_audi COUNT PERCENT);

DEFINE Grand_Total / DISPLAY ' ';
DEFINE is_audi / DISPLAY 'Is this car an Audi?';
DEFINE COUNT / DISPLAY 'Count' format=Comma10.0;
DEFINE PERCENT / DISPLAY 'Percent %' format=Comma10.3;

run;
TITLE;
ods excel options(sheet_interval='none');

Note: This code should work w/o errors, but be sure to set your own file pathway for the excel output

Re: How do I highlight specific cells of summary data for excel output? Character and Numeric!

PaigeMiller — Mon, 27 Mar 2023 19:29:43 GMT

proc report data=car_total;
    columns grand_total is_audi count percent;
    compute is_audi;
        if is_audi='Yes' then call define(_col_,'style','style={background=yellow}');
    endcompute;
    compute count;
        if is_audi='Yes' then call define(_col_,'style','style={background=yellow}');
    endcompute;
run;

Re: How do I highlight specific cells of summary data for excel output? Character and Numeric!

mreynaud — Mon, 27 Mar 2023 21:18:01 GMT

Thank you for the help! It worked but it duplicated the table... Very strange

Here was what I ran for the report portion:

ODS listing close;
ODS excel FILE=&resultfile STYLE=htmlblue
options(embedded_titles='yes' embedded_footnotes='yes' zoom='100' sheet_interval='none' sheet_name='Summary' suppress_bylines='yes'  
orientation='landscape' fittopage="no" Pages_FitWidth = '2' Pages_FitHeight = '4' autofilter='yes' 	row_repeat='header'
 embedded_footnotes='yes');
 
proc report data=car_total split='@';

TITLE "Car Summary";

COLUMN (Grand_Total is_audi COUNT PERCENT);

DEFINE Grand_Total / DISPLAY ' ';
DEFINE is_audi / DISPLAY 'Is this car an Audi?';
DEFINE COUNT / DISPLAY 'Count' format=Comma10.0;
DEFINE PERCENT / DISPLAY 'Percent %' format=Comma10.3;
columns grand_total is_audi count percent;

compute is_audi;
if is_audi='Yes' then call define(_col_,'style','style={background=yellow}');
    endcomp;
compute count;
        if is_audi='Yes' then call define(_col_,'style','style={background=yellow}');
    endcomp;
run;
TITLE;
ods excel options(sheet_interval='none');

Re: How do I highlight specific cells of summary data for excel output? Character and Numeric!

PaigeMiller — Mon, 27 Mar 2023 21:22:07 GMT

Perhaps because the COLUMNS statement appears twice in your code?

Re: How do I highlight specific cells of summary data for excel output? Character and Numeric!

mreynaud — Mon, 27 Mar 2023 21:25:35 GMT

Silly me! Thank you so much