BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ljaeger
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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;


output_example_xp.png

View solution in original post

3 REPLIES 3
Reeza
Super User

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
SAS Super FREQ

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;


output_example_xp.png
ljaeger
Fluorite | Level 6

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.

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!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1353 views
  • 2 likes
  • 3 in conversation