topic Re: Is there a way to tell Excel (via proc template) to repeat headers?? in ODS and Base Reporting

Is there a way to tell Excel (via proc template) to repeat headers??

ljaeger — Mon, 07 Apr 2014 16:21:01 GMT

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;

RUN;

ods listing close;

ods Tagsets.minimal file='c:\users\ljaeger\saspgm\bhp\reports\hr_all_access.xls' alias='landscape';

Re: Is there a way to tell Excel (via proc template) to repeat headers??

Reeza — Mon, 07 Apr 2014 16:32:33 GMT

Do you have to use this particular tagset, MSOFFICE2K_x?

If you can use Tagset.excelxp you can use the row_repeat option.

Re: Is there a way to tell Excel (via proc template) to repeat headers??

Cynthia_sas — Tue, 08 Apr 2014 00:02:03 GMT

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;

Re: Is there a way to tell Excel (via proc template) to repeat headers??

ljaeger — Tue, 08 Apr 2014 17:01:04 GMT

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.