topic Re: Controlling borders in PROC REPORT and ODS Excel in ODS and Base Reporting

Controlling borders in PROC REPORT and ODS Excel

bstarr — Tue, 20 Jul 2021 17:12:54 GMT

Hello,

I am attempting final formatting on reports generated using PROC REPORT and ODS Excel. I am trying to remove borders that appear between two rows from grouped data, like this:

Sample code:

data test;
set sashelp.cars (keep=Make Model MSRP MPG_City MPG_Highway);
    where Model in (' A4 1.8T 4dr' ' 325i 4dr');
    MPG = MPG_City; output;
    MPG = MPG_Highway; output;
run;
data test2;
set test (in=a) test (in=b);
    if a then year = '2019';
    if b then year = '2020';
run;

ods excel file="C:\Sample Proc Report.xlsx";
proc report data=test2 nowd spanrows style(hdr)=[fontfamily="Arial" fontsize=9pt foreground=white background=darkblue just=c] 
    style(lines)=[fontfamily="Arial" fontsize=9pt just=c borderbottomwidth=0 borderbottomstyle=hidden bordertopwidth=0 bordertopstyle=hidden];
    title "Title";
    columns ("Header1" Year Make ("Header2" MPG) MSRP);
        define Year / "Year" group order=data style(column)=[cellwidth=.65in];
        define Make / "Make" group order=data style(column)=[cellwidth=.4in];
        define MPG / "MPG" display style(column)=[cellwidth=.65in /*borderbottomstyle=hidden bordertopstyle=hidden*/];
        define MSRP / "MSRP" group style(column)=[cellwidth=.65in];
    compute after Make / style=[bordertopcolor=black bordertopwidth=1pt bordertopstyle=solid borderbottomcolor=black borderbottomwidth=1pt borderbottomstyle=solid];
        call define(_row_,'style','style=[bordertopstyle=solid bordertopwidth=1pt bordertopcolor=black borderbottomstyle=solid borderbottomwidth=1pt borderbottomcolor=black]');
    endcomp;
    compute after/style=[just=l fontfamily="Arial" color=black backgroundcolor=white borderbottomstyle=hidden borderleftstyle=hidden borderrightstyle=hidden];
        line "Footer goes here.";
    endcomp;
run;
ods excel close;

I've commented out a portion on the "define MPG" row that when uncommented, removes ALL borders for that column (but the borders don't reappear in my "compute after Make" block). So I can either remove all row borders for that column, or leave all row borders in - I can't seem to remove those alternating row borders.

What am I missing?

Thank you,

Brian

Re: Controlling borders in PROC REPORT and ODS Excel

Rydhm — Tue, 20 Jul 2021 18:40:17 GMT

your problem seems similar to this one:
https://communities.sas.com/t5/ODS-and-Base-Reporting/BORDER-ods-Excel-Proc-report-break-after-column/td-p/453471

Try the above solution.

Re: Controlling borders in PROC REPORT and ODS Excel

bstarr — Tue, 20 Jul 2021 19:08:39 GMT

Thanks for referencing that question/solution. Unfortunately, that code marked as a solution does not appear to work, at least in my version of SAS (9.4 TS1M6). It throws errors, and when I fix some of the errors, it still trips up saying that the subroutine "style" is unknown. The next logical leap I can think of is to use call define style, which I've already tried.

Re: Controlling borders in PROC REPORT and ODS Excel

Rydhm — Tue, 20 Jul 2021 21:13:34 GMT

Since it appears to be a conditional border, that can be done by adding a new variable prior to reporting. This variable is set to 1 for the last entry of each group. See code below:

data test;
set sashelp.cars (keep=Make Model MSRP MPG_City MPG_Highway);
    where Model in (' A4 1.8T 4dr' ' 325i 4dr');
    MPG = MPG_City; output;
    MPG = MPG_Highway; output;
run;
data test2;
set test (in=a) test (in=b);
    if a then year = '2019';
    if b then year = '2020';
run;

PROC SORT data=test2;
BY Make year;
RUN;

DATA test3;
SET test2;
BY Make year NOTSORTED;
IF LAST.Make or last.year THEN rowline=1;
RUN;

ods excel file="H:\Sample Proc Report.xlsx";
proc report data=test3 nowd spanrows style(hdr)=[fontfamily="Arial" fontsize=9pt foreground=white background=darkblue just=c] 
    style(lines)=[fontfamily="Arial" fontsize=9pt just=c borderbottomwidth=0 borderbottomstyle=hidden bordertopwidth=0 bordertopstyle=hidden]
    ;
    title "Title";
    columns ("Header1" Year Make ("Header2" MPG) MSRP) rowline;
        define Year / "Year" group order=data style(column)=[cellwidth=.65in];
        define Make / "Make" group order=data style(column)=[cellwidth=.4in];
        define MPG / "MPG" display style(column)=[cellwidth=.65in borderbottomcolor=white bordertopcolor=white] ;
        define MSRP / "MSRP" group style(column)=[cellwidth=.65in];
		DEFINE ROWLINE / ANALYSIS NOPRINT;
	COMPUTE rowline;
		 if rowline.sum=1 then
		 CALL DEFINE (_row_,'style',
		 'style=[borderbottomcolor=lightgrey' ) ;
	ENDCOMP;
	
    compute after Make / style=[bordertopcolor=black bordertopwidth=1pt bordertopstyle=solid borderbottomcolor=black borderbottomwidth=1pt borderbottomstyle=solid];
        call define(_row_,'style','style=[bordertopstyle=solid bordertopwidth=1pt bordertopcolor=black borderbottomstyle=solid borderbottomwidth=1pt borderbottomcolor=black]');
    endcomp;
    compute after/style=[just=l fontfamily="Arial" color=black backgroundcolor=white borderbottomstyle=hidden borderleftstyle=hidden borderrightstyle=hidden];
        line "Footer goes here.";
    endcomp;
run;
ods excel close;

Re: Controlling borders in PROC REPORT and ODS Excel

bstarr — Tue, 20 Jul 2021 21:46:33 GMT

Yes that is perfect, thank you!