The SAS Output Delivery System and reporting techniques

excelXP frozen_headers='yes'

Valued Guide
Posts: 2,175

excelXP frozen_headers='yes'

in tagsets.excelXP v1.94

seems like frozen_headers='yes' ignores the setting of embedded_titles='yes', or am I missing something?

From "doc='help' : [pre]Frozen_Headers: Default Value 'No'
Values: yes, no, true, false, number.
If 'Yes' The rows down to the bottom of the headers will be frozen when
the table data scrolls. This includes any titles created with the
embedded titles option. If a number is given, that is the row count
that will be frozen[/pre]

So I expected I could lose my "title counter" macro that works out where the column headers will come.
Only the top row is frozen.
Here is a brief demo:
ods _all_ close;
title 'demo title1' ;
title2 'frozen_headers=''yes'' ';
ods tagsets.excelxp file='demo.xml.xls' options(sheet_name='frozen_demo' autofilter='all' embedded_titles='yes' Frozen_Headers='yes') ;
proc print data= sashelp.class ;
run ;
proc sql noprint ;* obtaining the number of title lines ;[pre] select max( number)
into :n_titles separated by ' '
from sashelp.vtitle
where type='T'
;[/pre] %let head_at =%eval( 2+&n_titles) ; *proc print column headers are 2 lines after last title ;
quit ;
title2 "frozen_headers='&head_at' ";
ods tagsets.excelxp options(sheet_name='frozen_demo2' autofilter='all' embedded_titles='yes' Frozen_Headers="&head_at") ;
proc print data= sashelp.class ;
run ;
ods _all_ close;

what am I missing?
SAS Employee
Posts: 95

Re: excelXP frozen_headers='yes'

This does appear to be broken.

Here are the changes to make it work. I'll have a new version out as soon as I can.

The T should be capitalized.

define event add_title_rowcounts;
/*-- Sure would be nice to be able to do this. --*/
/*-- eval $nskip ^^$skip_factor['title']; --*/
do /if $skip_factor['Title'];

Titles should not be unset at the end of this event...

define event embedded_title;
break /if ^$embedded_titles;
break /if ^$titles;


/* unset $titles */
Valued Guide
Posts: 2,175

Re: excelXP frozen_headers='yes'

many thanks
Running the excelbase template code with these changes resulted in the correct frozen headers, but ...
... perhaps I shouldn't expect to edit the code in the template editor.
suddenly the style became different/minimalist
and it looked like some continued (overflowed) lines joined up a little differently than in the original.
So, I'll look forward to the update, when you can fix it!

Many thanks Eric

Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation