BookmarkSubscribeRSS Feed
deleted_user
Not applicable
When exporting a dataset to excell using proc export, how can I add headers and/or footers to that excell sheet using sas code ?
3 REPLIES 3
JuanVte
Calcite | Level 5
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
SAS Super FREQ
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
Not applicable
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
🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 3 replies
  • 2271 views
  • 0 likes
  • 3 in conversation