The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

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-comSmiley Surprisedffice: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 " <xSmiley Very HappyisplayPageBreak/>" NL;

put " <xSmiley Very HappyisplayZeros/>" NL;

PUT " <xSmiley Tonguerint>" NL;

PUT " <x:ValidPrinterInfo/>" NL;

put " <xSmiley Frustratedcale>70</xSmiley Frustratedcale>";

PUT " </xSmiley Tonguerint>" 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';


Accepted Solutions
Solution
‎04-07-2014 08:02 PM
SAS Super FREQ
Posts: 8,643

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

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

Attachment

All Replies
Grand Advisor
Posts: 16,307

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

Do you have to use this particular tagset, MSOFFICE2K_x?

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

Solution
‎04-07-2014 08:02 PM
SAS Super FREQ
Posts: 8,643

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

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;

Attachment
Occasional Contributor
Posts: 10

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

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.

Post a Question
Discussion Stats
  • 3 replies
  • 542 views
  • 2 likes
  • 3 in conversation