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
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;
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.
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;
Yes that is perfect, thank you!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.