topic Re: Printing title on top of worksheet in ODS and Base Reporting

Printing title on top of worksheet

JasonNC — Thu, 20 Oct 2016 21:49:36 GMT

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

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;

Re: Printing title on top of worksheet

Reeza — Thu, 20 Oct 2016 22:23:28 GMT

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

Re: Printing title on top of worksheet

JasonNC — Thu, 20 Oct 2016 22:42:28 GMT

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

Re: Printing title on top of worksheet

Reeza — Thu, 20 Oct 2016 22:47:46 GMT

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.

Re: Printing title on top of worksheet

Ksharp — Fri, 21 Oct 2016 02:09:44 GMT

Can you use ?

ods text='XXXXXXXXXXXXXXXXXX';

Re: Printing title on top of worksheet

JasonNC — Fri, 21 Oct 2016 05:00:28 GMT

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

Re: Printing title on top of worksheet

JasonNC — Fri, 21 Oct 2016 13:55:04 GMT

I think i Can't

Re: Printing title on top of worksheet

ballardw — Fri, 21 Oct 2016 15:15:03 GMT

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.

Re: Printing title on top of worksheet

JasonNC — Fri, 21 Oct 2016 15:20:56 GMT

IS there any way to overcome that

Re: Printing title on top of worksheet

Reeza — Fri, 21 Oct 2016 15:52:48 GMT

Use ODS TEXT instead of TITLE as @Ksharp suggested.

Re: Printing title on top of worksheet

JasonNC — Fri, 21 Oct 2016 16:01:19 GMT

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

Re: Printing title on top of worksheet

Cynthia_sas — Fri, 21 Oct 2016 18:38:25 GMT

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

Re: Printing title on top of worksheet

Reeza — Fri, 21 Oct 2016 19:21:00 GMT

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.

Re: Printing title on top of worksheet

JasonNC — Fri, 21 Oct 2016 21:41:22 GMT

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