ods tagsets.excelxp - Header or something

[buechler66]

Hi. Maybe I don't want to use the header option, but that's what I decided to try in this case. Any alternative suggestions would also be greatly appreciated since the header option isn't giving me the desired results.

My ultimate goal is to have the contents of the .xml file display the current &ZIP5 value. For now, I'm able to name each .xml file with a naming convention that reflects this, but once someone opens the .xml file there is no indication of the &ZIP5 value in the output. In essence, they must remember the filename when reviewing the file contents.  I just need whomever opens the .xml file to be able to see somewhere that it the data is for &ZIP5 on each page. This is why I thought using the header option made sense.

My problem with using the Header option is as follows:

I have a program that creates .xml files. I'm attempting to add a simple header to display the &ZIP5 value by using the following statements inside my program:

options(embedded_titles='yes');


title "IBM PTH Report - &ZIP5 Sample";

Unfortuneatly my output data's width is very long and so multiple pages end up being created when it's opened in Excel. The result is that my header is only appearing ONCE across the width of multiple pages.  For instance, if my output is so long that it spans 5 pages when I open it in Excel then the header only appears centered on page 3 of the output. I was expecting it to appear centered on all 5 pages.

Here is the full output code:

	* Output to XML Excel ready format ;
	options papersize='Legal';
	ods tagsets.excelxp file="/home/ssbuechl/IBM_Test_Harness_Comparison_Report_&ZIP5.._&rundate..xml" 
						style=statistical options(orientation='landscape' gridlines='yes')
						options(embedded_titles='yes');
	title "IBM PTH Report - &ZIP5 Sample";
	proc print noobs data=FinalData&ZIP5;
		* format Rule_Order best6. total comma10. _: comma10. null comma10.; 
		var rule_order score_impacting rule_nm; 
		var total _: null / style={tagattr="format:###,###,###"};
	run;
	ods tagsets.excelxp close;

[ChrisNZ]

There is no reason that the title should appear several times for one output.

That Excel split the data when viewing is irrelevant, and the split can happen anywhere depending on Excel's setup and even on your monitor's size. So what is a "page"?

[buechler66]

By page I meant I want it to appear once per page after I open it in excel,
after excel splits the output into multiple pages, so that the user can
always see the &ZIP5 value when viewing the file in Excel.

Would their be a way to give the XLS tab a name that included the &ZIP5
value?

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Sent from mobile

[ChrisNZ]

There is no page. Excel will split depending on the screen size, the zoom level, the font, etc.

Try option sheet_name= (up to 31 characters).

[buechler66]

How about this. Is it possible to somehow output a single record with the value &ZIP5 that would appear just about my outputs column names? So basically an extra row at the top of my output where I could fill cell A1 with the &ZIP5 value? At least this way the user would see the &ZIP5 value when they print the output in Excel.

[ChrisNZ]

Maybe add this?

title2 j=left " &ZIP5 ";

[buechler66]

Thanks. That helped quite a bit!

[Cynthia_sas]

Hi, And, if you are using BY group processing, you can put the BY group variable as the sheet name. Look at the screen shot and the picture in this posting:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Creating-Different-Worksheet-for-Each-Group-By...

  Excel headers only show when you go into Print Preview or actually print the file. Here's an example:

cynthia