Excell Headers & Footers

[deleted_user]

When exporting a dataset to excell using proc export, how can I add headers and/or footers to that excell sheet using sas code ?

[JuanVte]

Hi Rix,

When I want to export a table to an excel file I'm used to do this:

ods html file="c:\temp\demo.xls";

title1 "This is a title for my excel sheet";

proc print data=sashelp.class noobs;
run;

footnote "And this is a footnote :)";

ods html close;


In this way I can use the title and footnote command as always.

[Cynthia_sas]

Hi:
Also, if you use the ExcelXP tagset:
[pre]
ods tagsets.excelxp file="c:\temp\demo.xls";

title1 "Title for my SpreadsheetML XML file that will open with Excel";

proc print data=sashelp.class noobs;
run;

footnote "And this is a footnote ";

ods tagsets.excelxp close;
[/pre]

You will find that the title goes automatically into the Header area of the workbook (you can find it by looking in Page Setup).

If you get an updated copy of TAGSETS.EXCELXP (and create an updated copy of TAGSETS.EXCELXP in the SASUSER.TEMPLAT template store) , then the SAS footnote will automatically go into the footer area of the workbook.

To find out more about updating TAGSETS.EXCELXP, refer to the information on this site:
http://support.sas.com/rnd/base/ods/odsmarkup/

If you do not want anything as fancy as a SpreadsheetML file, then you might try a simple CSVALL file:

[pre]
ods csvall file="c:\temp\demo_csv.csv";

title1 "Title for my CSV file that will open with Excel";

proc print data=sashelp.class noobs;
run;

footnote "And this is a footnote ";

ods csvall close;
[/pre]

In this case, your title and footnote will go into the body of the CSV file (and therefore into the body of the Excel file).

cynthia

[deleted_user]

Hi Cynthia, this works great for SAS 9, I actually wanted to do this for SAS 8.2. I have SAS 9 available now so problem solved.

Many Thanks
🙂