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

ljaeger · Posted 04-07-2014 12:21 PM

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';

Cynthia_sas · Posted 04-07-2014 08:02 PM

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;

View solution in original post

Reeza · Posted 04-07-2014 12:32 PM

Do you have to use this particular tagset, MSOFFICE2K_x?

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

Cynthia_sas · Posted 04-07-2014 08:02 PM

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;

ljaeger · Posted 04-08-2014 01:01 PM

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.

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

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

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

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

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

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

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

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

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

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

2025 SAS Hackathon: There is still time!

SAS Training: Just a Click Away