topic Re: ods tagsets.excelxp - Header or something in SAS Programming

ods tagsets.excelxp - Header or something

buechler66 — Tue, 26 Jul 2016 15:04:21 GMT

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;

Re: ods tagsets.excelxp - Header or something

ChrisNZ — Wed, 27 Jul 2016 00:41:00 GMT

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"?

Re: ods tagsets.excelxp - Header or something

buechler66 — Wed, 27 Jul 2016 02:50:52 GMT

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

Re: ods tagsets.excelxp - Header or something

ChrisNZ — Wed, 27 Jul 2016 03:05:30 GMT

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).

Re: ods tagsets.excelxp - Header or something

buechler66 — Wed, 27 Jul 2016 13:15:38 GMT

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.

Re: ods tagsets.excelxp - Header or something

ChrisNZ — Wed, 27 Jul 2016 22:40:43 GMT

Maybe add this?

title2 j=left " &ZIP5 ";

Re: ods tagsets.excelxp - Header or something

buechler66 — Fri, 29 Jul 2016 16:47:31 GMT

Thanks. That helped quite a bit!

Re: ods tagsets.excelxp - Header or something

Cynthia_sas — Fri, 29 Jul 2016 20:12:59 GMT

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-Variables-in-SAS/m-p/285296/highlight/true#M16385

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

cynthia