topic Re: SAS 9.3 Excel Export with footer in SAS Programming

SAS 9.3 Excel Export with footer

sthomasw44 — Wed, 10 Aug 2016 13:28:40 GMT

I am using base SAS 9.3 and have a dataset that I need to export to excel (xlsx extension because rows are > 65k). I want to include a footer on the exel output. Please advise how to add a footer to a xlsx file.

Re: SAS 9.3 Excel Export with footer

PBsas — Wed, 10 Aug 2016 13:48:15 GMT

I don't believe you would be able to add footnote with proc export.

I recommend using ODS with footnote.

Re: SAS 9.3 Excel Export with footer

RW9 — Wed, 10 Aug 2016 13:55:01 GMT

You can put footnotes in, using footnote statement, however they will only appear in print preview in Excel - that is Excel functionality not SAS! If you want it in the grid - which is not Excel method - then add another row or two to your dataset, with the footnote you want, e.g.

data want;

set have;

col1=" ";

col2="A footnote";

run;

(Col2 being the first variable in your dataset - assumed its called this, and that it is character).

It will then get printed per the data. Excel itself doesn't have titles/footnotes in the same way as RTF or PDF.

Re: SAS 9.3 Excel Export with footer

sthomasw44 — Wed, 10 Aug 2016 14:38:26 GMT

I am pretty new to SAS ods so could you walk me through it so I can add footer to my final xlsx output?

Re: SAS 9.3 Excel Export with footer

PBsas — Wed, 10 Aug 2016 19:32:46 GMT

Hi,

Since you are using 9.3 version of the SAS, you won't be able to use ODS excel to create .xlsx file. You can create .xls file using ODS tagsets.excelXP with a footnote.

ods _all_ close;
ods tagsets.excelxp path = "path_here"  file = "file_name.xls" style = XLsansPrinter 


	options(embedded_footnotes = "yes");
footnote "Your footnote here";

proc print data=dataset_name noobs;
run;

ods tagsets.excelxp close;