The SAS Output Delivery System and reporting techniques

ODS ExcelXP Tagset and Title Statement

Reply
New Contributor
Posts: 2

ODS ExcelXP Tagset and Title Statement

When I run a PROC REPORT in ODS tagsets.ExcelXP using aTitle statements, the Row Height of a Title statement that contains a date will get quite large.  Here is my code:

When the file is opened in Excel, the row height that has the Title3 statement is 198.  How do I get this row to a more reasonable height?  Note: If I remove the dates, the row height becomes normal.  The dates can seemingly be in any format (mm/dd/yyyy, ddmmmyyyy, January 1, 2001).

Help!!

ODS tagsets.ExcelXP PATH=&g_ODS_Path  FILE=&g_ODS_File STYLE=sasweb

OPTIONS(frozen_headers        = '6'

        autofit_height        = 'yes'

        absolute_column_width = '35,21,8,14,14,14,14,14,14'

        center_horizontal     = 'yes'

        merge_titles_footnotes= 'yes'

        orientation           = 'landscape'

        print_header_margin   = '.0'

        print_footer_margin   = '.0'

        left_margin           = '.5'

        right_margin          = '.5'

        sheet_interval        = 'none'

        sheet_name            = "YTD &g_Year"

        embedded_footnotes    = 'no'

        embedded_titles       = 'Yes');

TITLE1;

TITLE2 JUSTIFY=center ITALIC COLOR=gray FONT=Arial HEIGHT=12pt 'LSS Balancing Report';

TITLE3 JUSTIFY=center ITALIC COLOR=gray FONT=Arial HEIGHT=8pt "Activity Period: 1/1/2011 - 1/31/2011              Run Date: 8/2/2011 ";

TITLE4 JUSTIFY=center ITALIC COLOR=gray FONT=Arial HEIGHT=6pt "Entered By: ALL - Entity: 8.ALL - HCP ID: 3. ALL_SYS - Report Type: 2. All_Consolidated - System Type: 3. ALL - Program Name: LSS_Balancing_Report.sas";


   PROC report DATA=SummaryReport&g_Counter SPLIT='_' NOWD;
      COLUMN cat1 cat2 row_group_hdg GroupType Count Pmt_Posted  Net_Cash_Fees  Invoice_Generating_Fees  NSF_Fees  Adjustment_Entry_Fees  Net_Transaction_Amount;
      DEFINE cat1 / order noprint;
      DEFINE cat2 / order noprint;
      DEFINE row_group_hdg           / STYLE(column)=[FONT_FACE=Arial FONT_SIZE=1] 'Type';
      DEFINE GroupType               / STYLE(column)=[FONT_FACE=Arial FONT_SIZE=1] 'Pmt_Type';
      DEFINE Count                   / STYLE(column)={FONT_FACE=Arial FONT_SIZE=1 tagattr='format:###,###,##0'}     'Count';
      DEFINE Pmt_Posted              / STYLE(column)={FONT_FACE=Arial FONT_SIZE=1 tagattr='format:$###,###,##0.00'} 'Pmt Posted';
      DEFINE Net_Cash_Fees           / STYLE(column)={FONT_FACE=Arial FONT_SIZE=1 tagattr='format:$###,###,##0.00'} 'Net Cash Fees';
      DEFINE Invoice_Generating_Fees / STYLE(column)={FONT_FACE=Arial FONT_SIZE=1 tagattr='format:$###,###,##0.00'} 'Invoice Generating Fees';
      DEFINE NSF_Fees                / STYLE(column)={FONT_FACE=Arial FONT_SIZE=1 tagattr='format:$###,###,##0.00'} 'NSF Fees';
      DEFINE Adjustment_Entry_Fees   / STYLE(column)={FONT_FACE=Arial FONT_SIZE=1 tagattr='format:$###,###,##0.00'} 'Adjustment Entry Fees';
      DEFINE Net_Transaction_Amount  / STYLE(column)={FONT_FACE=Arial FONT_SIZE=1 tagattr='format:$###,###,##0.00'} 'Net Transaction Amount';
      BREAK after cat2 / STYLE=[BACKGROUND=BLUE FOREGROUND=WHITE FONT_WEIGHT=BOLD FONT_SIZE=1] SUMMARIZE;
   run;

ODS _all_ CLOSE;

SAS Super FREQ
Posts: 8,868

Re: ODS ExcelXP Tagset and Title Statement

Hi:

  There is a ROW_HEIGHTS suboption that allows you to specifically provide a row height for the title. If you use doc='Help' as a suboption, you will be able to see the current list of suboptions.

  However, when I run a version of your code (same title statements and suboptions, different data), I do not observe what you describe (see screen shot -- my row height for TITLE3 is set at 18, not 198) -- perhaps it would be better to open a track with Tech Support on this issue.

cynthia


row_height_xp.jpg
Super Contributor
Posts: 273

Re: ODS ExcelXP Tagset and Title Statement

Posted in reply to Cynthia_sas

Cynthia

I agree with the sender that   something is not working  (under excel2010 from sas 9.2.3  latest tagset )

with the same code -title and options- with a proc print    see result image

and it is coming from any content that approach from a date  even if you withdraw : or

put spaces between  / or -  in the text of the title3

Andre 

2011-08-04_153531.jpg

Super Contributor
Posts: 273

Re: ODS ExcelXP Tagset and Title Statement

follow up

and changing

Row_Heights           = '0,0,0,10,0,0,0'

        autofit_height        = 'no

changes nothing'

SAS Super FREQ
Posts: 8,868

ODS ExcelXP Tagset and Title Statement

Hi:

  I was running the same version of SAS (9.2.3) and Excel 2010. That's why I recommended opening a track with Tech Support. They can determine whether it is a SAS issue or not and if the tagset template needs to change, they can work with the developers to get a fix started.

cynthia

New Contributor
Posts: 2

ODS ExcelXP Tagset and Title Statement

Posted in reply to Cynthia_sas

Thanks for the replies.  Based on Cynthia's original reply, I have opened a Track with Tech Support.

FWIW: We have SAS 9.2 M3 installed on a Windows 2008 x64 server.  We currently run Office 2003.

I have been playing with the ROW_HEIGHT option along with AUTOFIT_HEIGHT on/off.  There doesn't seem to be any combination that will give me good row heights.

Andre, I'm glad (sort of) that you also got the same result as me.  At least I know it's not just me!!

Bill

N/A
Posts: 1

Re: ODS ExcelXP Tagset and Title Statement

Posted in reply to Cynthia_sas

Cynthia--

Was this ever resolved?  I'm having the same issue.

Thanks.

SAS Super FREQ
Posts: 8,868

ODS ExcelXP Tagset and Title Statement

Hi:

  I never experienced the issue -- see my screen shot in the post above. That's why I recommended opening a track with Tech Support. If you are having the same issue, then I recommend working with Tech Support. They can look at previous tracks and involve the developers, if this is an intermittent bug or related to a style template or ??? Also, they can write a Tech Support note, if warranted.

  To send a question to Tech Support, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem". Alternately, you can go directly to the Tech Support Problem Form here:

http://support.sas.com/ctx/supportform/createForm

cynthia

Occasional Contributor
Posts: 5

Re: ODS ExcelXP Tagset and Title Statement

I had the same problem with the title row height issue, but it was resolved when I replaced the ExcelXP tagset with the latest version (v1.127, dated 9/26/11).

For the record, I'm running SAS v9.2 on SunOS and was using Excel 2007.

Ask a Question
Discussion stats
  • 8 replies
  • 3531 views
  • 0 likes
  • 5 in conversation