topic Re: How to add a formatted line at the end of a proc report table? in ODS and Base Reporting

How to add a formatted line at the end of a proc report table?

Multipla99 — Wed, 09 Nov 2022 12:52:52 GMT

I'm trying to produce some nice looking tables with the help of ODS Excel and Proc Report. You can see what I have acheived so far in the xlsx-file attached.

However, now I would like to add a line at the end of the table. The line should be of the same format as the lines above and below the colum headers row.

Can someone please advise me how to this? The code I'm using is attached below.

ods _all_ close;
goptions device=png;
ods excel
file="C:\Temp\SAS data.xlsx"
;
ods excel
options(sheet_name = "Class"
sheet_interval='none'
start_at = "B2"
absolute_column_width = "6,15,6,6,10,10")
;

proc odstext;
p 'Data Set Class'
/ style =[fontsize=8pt fontfamily=roboto fontweight=bold];
run;

proc report
data = sashelp.class
style(header)=[borderbottomcolor = #1E00BE
borderbottomstyle = solid
borderbottomwidth = 10pt
bordertopcolor = #1E00BE
bordertopstyle = solid
bordertopwidth = 10pt
fontsize=8pt fontfamily=roboto]
style(column)=[fontsize=8pt fontfamily=roboto vjust=c]
completerows
;
columns Name Sex Age Height Weight;
define Name
/
style(header)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Sex
/
style(header)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Age
/
style(header)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Height
/
style(header)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Weight
/
style(header)={just=r}
style(column)={just=r};
run;

ods excel close;
goptions reset=goptions;

Re: How to add a formatted line at the end of a proc report table?

Ksharp — Thu, 10 Nov 2022 11:45:47 GMT

ods _all_ close;
goptions device=png;
ods excel
file="C:\Temp\SAS data.xlsx"
;
ods excel
options(sheet_name = "Class"
sheet_interval='none'
start_at = "B2"
absolute_column_width = "6,15,6,6,10,10")
;
proc odstext;
p 'Data Set Class'
/ style =[fontsize=8pt fontfamily=roboto fontweight=bold];
run;
proc report nowd
data = sashelp.class
style(header)=[borderbottomcolor = #1E00BE
borderbottomstyle = solid
borderbottomwidth = 10pt
bordertopcolor = #1E00BE
bordertopstyle = solid
bordertopwidth = 10pt
fontsize=8pt fontfamily=roboto]
style(column)=[fontsize=8pt fontfamily=roboto vjust=c]
completerows
;
columns Name Sex Age Height Weight;
define Name
/
style(header)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Sex
/
style(header)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Age
/
style(header)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Height
/
style(header)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Weight
/
style(header)={just=r}
style(column)={just=r};

compute after ;
Name="Toatl";Sex='X';age.sum=99;weight.sum=99;height.sum=99;
endcomp;
rbreak after/summarize;
run;
ods excel close;
goptions reset=goptions;

Re: How to add a formatted line at the end of a proc report table?

Multipla99 — Fri, 11 Nov 2022 07:42:28 GMT

Thank you, Ksharp! Not exactly what I asked for, but you put me on the right track.

What I finally did to solve the problem is that I added an extra line under the table, but without summarizing the values. Then I formatted the background and line colours of that line so that it gives an impression of a blue line at the end of the table.

It's not a perfect solution since there will be a redundant row in the table. However, I do not think there exists a real solution except from manually formatting the table in Excel after the output has been generated.

compute after 
/ 
style={bordertopcolor= #1E00BE 
bordertopwidth=10pt
bordertopstyle = solid 
backgroundcolor= #ffffff 
borderbottomcolor= #ffffff 
borderleftcolor= #ffffff
borderrightcolor= #ffffff};
line ' ';
endcomp;

Re: How to add a formatted line at the end of a proc report table?

Ksharp — Fri, 11 Nov 2022 11:44:04 GMT

You'd better post a picture to explain what you want to avoid misunderstanding .

goptions device=png;
ods excel
file="C:\Temp\SAS data.xlsx"  
;
ods excel
options(sheet_name = "Class"
sheet_interval='none'
start_at = "B2"
absolute_column_width = "6,15,6,6,10,10")
;
proc odstext;
p 'Data Set Class'
/ style =[fontsize=8pt fontfamily=roboto fontweight=bold];
run;

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

proc report nowd
data = sashelp.class
style(header)=[borderbottomcolor = #1E00BE
borderbottomstyle = solid
borderbottomwidth = 10pt
bordertopcolor = #1E00BE
bordertopstyle = solid
bordertopwidth = 10pt
fontsize=8pt fontfamily=roboto]
style(column)=[fontsize=8pt fontfamily=roboto vjust=c]
completerows
;
columns Name Sex Age Height Weight;
define Name
/
style(header)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Sex
/
style(header)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=l borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Age
/
style(header)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Height
/
style(header)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt}
style(column)={just=r borderrightcolor = #1E00BE
borderrightstyle = solid
borderrightwidth = 1pt};
define Weight
/
style(header)={just=r}
style(column)={just=r};


compute weight;
n+1;
if n=&nobs. then call define(_row_,'style','style={borderbottomcolor= #1E00BE borderbottomwidth=20}'); 
endcomp;
run;
ods excel close;
goptions reset=goptions;

Re: How to add a formatted line at the end of a proc report table?

Multipla99 — Fri, 11 Nov 2022 14:54:30 GMT

You managed to solve it, Ksharp!
I'm very impressed and, of course, most thankful.
Have a great day!