topic Re: Remove row in ods excel between Data and Footlines in SAS Programming

Remove row in ods excel between Data and Footlines

MarcoHonig — Tue, 09 Apr 2019 08:45:00 GMT

How do i remove the row between my Data and the Footnote in ODS EXCEL?

Here is the code:

ods excel

file="H:\DATA\Berechtigungsgruppen_nach_Department2.xls" 
options(autofilter="1-2" embedded_titles="yes" embedded_footnotes="yes" frozen_headers="yes"
sheet_name="Analyse Departments" ) ;
title color=black height=12pt 'Berechtigungsgruppen je Deparment';
footnote color=black height=10pt 'Summierung nach Departments und Berechtigungsgruppen';

proc report data=Eval_Department5 nowd spanrows
style(header)=[background=#A4A4A4 font_size=9pt color=black just=left vjust=c]
style(column)=[font_size=8pt just=left];
define Department / order style(column)={vjust=c just=left};
*define Department / display style(column)=[cellwidth=80pt];
define Gruppe / display style(column)=[cellwidth=142pt];
define Anzahl /display style(column)=[cellwidth=53pt just=right];
define Anzahl/analysis sum;
compute after ; 
Department ="Gesamt";
endcomp;

compute department;

if Department = 'Total' then
do;
department=department;
call define(_row_,'style','style={font_weight=bold font_size=8pt background=#A4A4A4 }');
end;
endcomp;

compute gruppe;

if gruppe = 'Gesamt' then
do;
gruppe=gruppe;
call define(_row_,'style','style={font_weight=bold font_size=8pt background=lightgrey }');
end;
endcomp;
run;

ods excel close;

Re: Remove row in ods excel between Data and Footlines

Ksharp — Tue, 09 Apr 2019 13:44:10 GMT

Try Use option POSTTEXT= instead of footnote.

proc report data=Eval_Department5 nowd spanrows style(report)=[posttext='my footnote' ]

Re: Remove row in ods excel between Data and Footlines

MarcoHonig — Wed, 10 Apr 2019 06:39:20 GMT

Thanks for your quick answer.

It also creates an extra row between my Data and the posttext.

Re: Remove row in ods excel between Data and Footlines

Ksharp — Wed, 10 Apr 2019 12:29:13 GMT

compute block ?

compute after;

 line 'My footnote' ;

endcomp;

Re: Remove row in ods excel between Data and Footlines

MarcoHonig — Wed, 10 Apr 2019 13:57:12 GMT

Same Problem. I guess it can be done with a proc template... But i did not find the right one yet