It seems like you ought to be able to add code like PUT "x:<RepeatRowHeader>A3:A5</RepeatRowHeaders>" into a template definition like the one below.
I asked SAS support this question and was told "No, you can't do it," but no suggestions of other things I might try to get repeating headers when passing
data to Excel via Proc Report. Thoughts?
PROC TEMPLATE;
DEFINE tagset tagsets.bhp;
parent=tagsets.MSOffice2K_x;
DEFINE event initialize;
set $orientation tagset_alias;
trigger set_just_lookup;
trigger set_nls_num;
end;
DEFINE event doc;
START: PUT '<html xmlns:x="urn:schemas-microsoft-com:office:excel">' NL;
FINISH: PUT "</html>" NL;
END;
DEFINE event doc_head;
START:
PUT "<head>" NL;
PUT "<style> @page {mso-page-orientation:landscape} </style>" NL;
PUT VALUE NL;
FINISH:
do / if cmp($orientation,'landscape');
PUT "<!--[if gte mso 9]><xml>" NL;
PUT "<x:ExcelWorkbook>" NL;
PUT " <x:ExcelWorksheets>" NL;
PUT " <x:ExcelWorksheet>" NL;
PUT " <x:Name>Utilization</x:Name>" NL; *******change;
PUT " <x:WorksheetOptions>" NL;
put " <x:DisplayPageBreak/>" NL;
put " <x:DisplayZeros/>" NL;
PUT " <x:Print>" NL;
PUT " <x:ValidPrinterInfo/>" NL;
put " <x:Scale>70</x:Scale>";
PUT " </x:Print>" NL;
PUT " </x:WorksheetOptions>" NL;
PUT " </x:ExcelWorksheet>" NL;
PUT "</x:ExcelWorkbook>" NL;
PUT "</xml><![endif]-->" NL;
done;
PUT "</head>" NL;
END;
END;
RUN;
ods listing close;
ods Tagsets.minimal file='c:\users\ljaeger\saspgm\bhp\reports\hr_all_access.xls' alias='landscape';
Hi:
TAGSETS.MSOFFICE2K_X is primarily an HTML tagset with some embedded XML that conforms to a specification that Microsoft used between Office 2000 and the introduction of Spreadsheet Markup Language in Office 2003. If Tech Support told you that changing the tagset template would not get you what you want, then the reason has to be that the Microsoft spec for the "hybrid" HTML/XML does not support what you want to do.
However, Reeza is correct that TAGSETS.EXCELXP has quite a few more suboptions than TAGSETS.MSOFFICE2K_X, including an option to repeat row headers. For example, if you run the code below, you should get the results as shown in the screen shot, which show the header rows repeated on page 6 of the output (in landscape mode).
Cynthia
ods tagsets.excelxp file='c:\temp\land_repeat.xml' style=minimal
options(row_repeat='header' orientation='landscape' doc='Help'
center_horizontal='yes' center_vertical='no');
proc report data=sashelp.cars nowd
style(header)={font_weight=bold font_style=italic};
column make model type mpg_highway mpg_city;
define model/ style(column)={cellwidth=2in};
define mpg_highway / style(column)={cellwidth=1.5in};
define mpg_city / style(column)={cellwidth=1.5in};
run;
ods tagsets.excelxp close;
Do you have to use this particular tagset, MSOFFICE2K_x?
If you can use Tagset.excelxp you can use the row_repeat option.
Hi:
TAGSETS.MSOFFICE2K_X is primarily an HTML tagset with some embedded XML that conforms to a specification that Microsoft used between Office 2000 and the introduction of Spreadsheet Markup Language in Office 2003. If Tech Support told you that changing the tagset template would not get you what you want, then the reason has to be that the Microsoft spec for the "hybrid" HTML/XML does not support what you want to do.
However, Reeza is correct that TAGSETS.EXCELXP has quite a few more suboptions than TAGSETS.MSOFFICE2K_X, including an option to repeat row headers. For example, if you run the code below, you should get the results as shown in the screen shot, which show the header rows repeated on page 6 of the output (in landscape mode).
Cynthia
ods tagsets.excelxp file='c:\temp\land_repeat.xml' style=minimal
options(row_repeat='header' orientation='landscape' doc='Help'
center_horizontal='yes' center_vertical='no');
proc report data=sashelp.cars nowd
style(header)={font_weight=bold font_style=italic};
column make model type mpg_highway mpg_city;
define model/ style(column)={cellwidth=2in};
define mpg_highway / style(column)={cellwidth=1.5in};
define mpg_city / style(column)={cellwidth=1.5in};
run;
ods tagsets.excelxp close;
Thanks so much for this! It worked, and specifying the file as xml, rather than xls, meant that the excel file opened right up rather than me having to tell excel to open it.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.