The SAS Output Delivery System and reporting techniques

Printing title on top of worksheet

Reply
Regular Contributor
Posts: 155

Printing title on top of worksheet

[ Edited ]

Hi,

 

I want to print the title only once on the top of the worksheet but title is being printed after each page break.How to print only on top of the work sheet?

 

I used embed_titles_once option but still the title is printed after each block 

 

As shown below title is being printed after each block which i don't want. I want to print only once on top of the sheet

 

 

 

 Picture.PNG

 

I am using Proc report and ods excel xp tagset

 

ods escapechar='^';
OPTIONS FORMCHAR="|----|+|---+=|-/\<>*" nobyline missing=0;
ods tagsets.excelxp file = "test..xls" ;
ods tagsets.ExcelXP options(autofit_height="yes" autofit_width="yes" sheet_interval="none"
absolute_column_width='12,6,9,8,8' embedded_titles="yes" embed_titles_once="yes" sheet_name="Stats");

title "Report Date &Dt";

proc report data=Final split='*' ls=200 missing

 

style(header)={background=mediumblue foreground=white fontsize=12pt fontweight=bold vjust=b}
style(summary)={background=mediumblue foreground=white fontweight=bold};

 

column ordr type units dollars  ;

 

define ordr / order order=data noprint;
define tyoe / order order=data ;
define units / order order=data ;

define dollars / order order=data ;

 

break after ordr / page;

 

compute before _page_ / style=Header{background=mediumblue foreground=white fontsize=12pt fontweight=bold};
line ' ';
endcomp;

run;

Super User
Posts: 17,831

Re: Printing title on top of worksheet

What version of tagsets are you using? You can see the version after you execute a tagsets code.

Regular Contributor
Posts: 155

Re: Printing title on top of worksheet

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013)
Super User
Posts: 17,831

Re: Printing title on top of worksheet

You're a version out of date.


Get the update here:

https://support.sas.com/rnd/base/ods/odsmarkup/index.html

 

See if that helps to resolve your issue. If not, post back. 

Regular Contributor
Posts: 155

Re: Printing title on top of worksheet

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.131, 04/23/2015

 

I updated the tagset but still getting the same issue

Super User
Posts: 9,681

Re: Printing title on top of worksheet

Can you use ?

ods text='XXXXXXXXXXXXXXXXXX';
Regular Contributor
Posts: 155

Re: Printing title on top of worksheet

I think i Can't

Super User
Posts: 10,500

Re: Printing title on top of worksheet

By default the title will appear on each PAGE of output that proc report generates: break after ordr / page; creates the pages.

 

Since your tagsets options puts all of the proc report logical pages on a single physical page then you get that result.

 

 

Regular Contributor
Posts: 155

Re: Printing title on top of worksheet

IS there any way to overcome that

Super User
Posts: 17,831

Re: Printing title on top of worksheet

Use ODS TEXT instead of TITLE as @Ksharp suggested. 

Regular Contributor
Posts: 155

Re: Printing title on top of worksheet

I tried it but in ods excel xp tagset it's not working 

SAS Super FREQ
Posts: 8,743

Re: Printing title on top of worksheet

Hi:
I think the logical page that you are causing by your BREAK statement, coupled with your sheet_interval option will always cause the title to appear before each separate table -- since every table is a logical page.

If ODS TEXT= does not work for you when you use it, then this seems to me to be a question for Tech Support to see if this is a defect or working as designed.

The only other alternative I can think of is the PRINT_HEADER= suboption, but that text will NOT appear inside the sheet, it will only appear in Print Preview and when the sheet is printed.

cynthia
Regular Contributor
Posts: 155

Re: Printing title on top of worksheet

Currently the work around I did was for the first row on the dataset created a title statement and printed it using compute before _page_ block so that it prints only for the first table

Super User
Posts: 17,831

Re: Printing title on top of worksheet

If you post your code with a dataset we can run, ie sashelp.class or sashelp.cars then we can try and help you debug. 

Ask a Question
Discussion stats
  • 13 replies
  • 546 views
  • 0 likes
  • 5 in conversation