BookmarkSubscribeRSS Feed
buechler66
Barite | Level 11

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;

 

 

7 REPLIES 7
ChrisNZ
Tourmaline | Level 20

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
Barite | Level 11
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
Tourmaline | Level 20

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
Barite | Level 11
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.
buechler66
Barite | Level 11
Thanks. That helped quite a bit!
Cynthia_sas
SAS Super FREQ

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:

using_ExcelXP_headers.png

 

cynthia

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
  • 7 replies
  • 1193 views
  • 3 likes
  • 3 in conversation