how to get bold bottom border when using proc report to export the data

[daisy6]

Hello SAS experts,

I want to get a report which has the bold bottom border when a column value changes. For example,

data want;
set sashelp.class;
run;
proc sort data=want;
by sex name;
run;
ODS noresults;
ODS listing close;
ods Excel file="&dir.\test_&mon..xlsx" options (Embedded_titles = 'yes' sheet_name= "Class" frozen_headers= '2'
frozen_rowheaders="9" row_heights="16pt,16pt,16pt,16pt" absolute_column_width= '8,8,8,16,8,8,8,8,8,8,8,8,8,8,8');
title1 j=l height=8pt font=Verdana color=CX000000 "Class by Gender ";
options missing=" ";
proc Report data=want NOWD
style(header)=[vjust=m font_weight=bold foreground=white background=CX007FA3 FONT=(Verdana, 8pt, BOLD)]
style(column)={vjust=m FONT=(Verdana, 8pt)};
Column name sex age weight height;
define name / center display "Name";
define sex / order order=data noprint;
define age / center display "Age";
define weight / center display "Weight";
define height / center display "height";

compute name;
count+1;
if (mod(count,2))=0 then do;
call define(_row_,"style","style=[background=CXD4EAE4]");
end;
endcomp;

compute after sex / style=[borderbottomwidth=1pt borderbottomcolor=black];
endcomp;
run;
ods _all_ close;
ODS listing;

When the gender changes from Female to male, I want the excel output of the last person Mary to have bold black bottom border. But my SAS code doesn't work. Could someone help me out? Thanks a lot!

[ballardw · Accepted solution]

Since you are looking for "last" behavior I think that you need to add an indicator variable to place the style element as desired. The indicator might want to be in the first column as below:

data want2;
   set want;
   by sex name;
   If Last.Sex and sex='F' then Lastsex=1;
run;
options missing=" ";
proc Report data=want2 NOWD
style(header)=[vjust=m font_weight=bold foreground=white background=CX007FA3 FONT=(Verdana, 8pt, BOLD)]
style(column)={vjust=m FONT=(Verdana, 8pt)};
Column lastsex name  age weight height;
define name / center display "Name";
define lastsex /display  noprint;
define age / center display "Age";
define weight / center display "Weight";
define height / center display "height";

compute lastsex;
   if lastsex=1 then do;
      call define(_row_,"style","style=[borderbottomwidth=1pt borderbottomcolor=black]");
   end;
endcomp;
compute name;
   count+1;
   if (mod(count,2))=0 then do;
      call define(_row_,"style","style=[background=CXD4EAE4]");
   end;
endcomp;

/*compute after lastsex / style=[borderbottomwidth=1pt borderbottomcolor=black];*/
/*endcomp;*/
run;
options missing='.';

From your description I don't think you want compute after as that is for summaries.

I did not include the ods excel, I'll leave that to you. Test the output before Excel as sometimes the translation to Excel causes odd things.

Depending 1PT may not be a thick enough line so be prepared to bump that to a larger number. Decimals are acceptable.

[daisy6]

It works.Thank you very much!