ODS EXCEL/PROC REPORT how to make thick lines at "breaks"

[PaigeMiller]

proc sort data=sashelp.class out=class;
by sex;
run;
ods excel file="text.xlsx";
proc report data=class;
columns sex age height weight;
run;
ods excel close;

How can I instruct PROC REPORT to produce a thick line between the last F and first M record? How can I instruct PROC REPORT to produce a thick line above the first F record? How can I instruct PROC REPORT to produce a thick line below the last M record? It should look like this, I made the lines thick using Excel, but I want to do in SAS:

[Ksharp · Accepted solution]

proc sort data=sashelp.class out=class;
by sex;
run;

%let dsid=%sysfunc(open(class));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
 
ods excel file="c:\temp\text.xlsx" ;
proc report data=class nowd   ;
columns sex age height weight;
compute weight;
n+1;
if sex ne lag(sex) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if  n=&nobs. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;

[PaigeMiller]

Great job, @Ksharp . Thanks! I was not aware that LAG would work in a COMPUTE block in PROC REPORT.

[PaigeMiller]

Ok, again thank you @Ksharp but I seem to have fallen into a trap that others fall into. I gave a data set that didn't illustrate the real problem. Sorry about that.

So how do I get this to work with two group variables and an across variable, with thick lines between the different values of TYPE? The code below doesn't work, the thick lines are not in the right spots.

%let dsid=%sysfunc(open(sashelp.cars));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
%put &=nobs;

ods excel file="c:\temp\text.xlsx" ;
proc report data=sashelp.cars nowd   ;
columns type drivetrain origin,(enginesize horsepower) _dummy;
define type/group;
define drivetrain/group;
define origin/across;
define enginesize/mean;
define horsepower/mean;
define _dummy/noprint;
compute _dummy;
n+1;
if type ne lag(type) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if n=&nobs. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;

Here is what I want the report to look like, but I did this in Excel, I want to know how to do this in SAS.

[Ksharp]

OK Paige,

How about this one ?

Opps, I missed one thing the black line under header.

@PaigeMiller  code has been updated.

proc sort data=sashelp.cars out=have;
by type drivetrain origin;
run;


proc freq data=have noprint;
table type*drivetrain /out=temp;
run;
data _null_;
 set temp end=last;
 by type;
 length nobs $ 100;
 retain nobs;
 if first.type then nobs=catx(' ',nobs,_n_);
 if last then do;call symputx('nobs',nobs);call symputx('n',_n_);end;
run;
%put &=nobs.;


ods excel file="c:\temp\text.xlsx" ;
proc report data=have nowd ;
columns type drivetrain origin,(enginesize horsepower) ;
define type/group;
define drivetrain/group;
define origin/across;
define enginesize/mean;
define horsepower/mean;
compute type;
n+1;
if n in ( &nobs.) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if n = &n. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;

[PaigeMiller]

Excellent! That works, and is perfectly general so I can apply it to similar problems.

As a side comment (I'm not asking for additional code since this works) I am surprised the solution is not done entirely in PROC REPORT, which is where I got stuck, I thought it would be possible, but I never got close.

[Ksharp]

@PaigeMiller 

Sorry . Actually PROC REPORT could get job done.

Here code is simpler .

proc sql noprint;
select count(*) into :nobs
 from (select distinct  type,drivetrain from sashelp.cars) ;
quit;

ods excel file="c:\temp\text.xlsx" ;
proc report data=sashelp.cars nowd   ;
columns type drivetrain origin,(enginesize horsepower) ;
define type/group;
define drivetrain/group;
define origin/across;
define enginesize/mean;
define horsepower/mean;
compute type;
n+1;
if not missing(type) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if n=&nobs. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;

[PaigeMiller]

Thank you again!

One thing you did in this code, which I didn't think of when I was trying to get this to work by myself, is that you have realized (which I didn't realize) that making a thick line above a certain cell is the same as going to the cell above it and making a thick line below that cell. Once I have that idea, yes, everything you did makes perfect sense and seems quite easy now.