topic Re: Excell Headers & Footers in SAS Health and Life Sciences

Excell Headers & Footers

deleted_user — Thu, 25 Oct 2007 10:59:22 GMT

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

Re: Excell Headers & Footers

JuanVte — Thu, 25 Oct 2007 15:07:53 GMT

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.

Re: Excell Headers & Footers

Cynthia_sas — Thu, 25 Oct 2007 19:37:35 GMT

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

Re: Excell Headers & Footers

deleted_user — Fri, 26 Oct 2007 08:32:45 GMT

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
🙂