topic Re: how to get bold bottom border when using proc report to export the data in ODS and Base Reporting

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

daisy6 — Wed, 23 Jan 2019 15:47:21 GMT

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!

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

ballardw — Wed, 23 Jan 2019 17:39:46 GMT

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.

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

daisy6 — Wed, 23 Jan 2019 18:55:29 GMT

It works.Thank you very much!