topic Re: ODS ExcelXP Tagset and Title Statement in ODS and Base Reporting

ODS ExcelXP Tagset and Title Statement

Bill_N — Wed, 03 Aug 2011 21:04:36 GMT

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;

Re: ODS ExcelXP Tagset and Title Statement

Cynthia_sas — Thu, 04 Aug 2011 13:13:42 GMT

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

Re: ODS ExcelXP Tagset and Title Statement

Andre — Thu, 04 Aug 2011 13:43:47 GMT

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

Re: ODS ExcelXP Tagset and Title Statement

Andre — Thu, 04 Aug 2011 13:46:02 GMT

follow up

and changing

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

autofit_height = 'no

changes nothing'

ODS ExcelXP Tagset and Title Statement

Cynthia_sas — Thu, 04 Aug 2011 14:01:30 GMT

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

ODS ExcelXP Tagset and Title Statement

Bill_N — Thu, 04 Aug 2011 14:13:09 GMT

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

Re: ODS ExcelXP Tagset and Title Statement

jrvanzee — Wed, 11 Jan 2012 23:04:33 GMT

Cynthia--

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

Thanks.

ODS ExcelXP Tagset and Title Statement

Cynthia_sas — Thu, 12 Jan 2012 06:19:08 GMT

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

Re: ODS ExcelXP Tagset and Title Statement

DavidMalerich — Tue, 10 Apr 2012 01:34:38 GMT

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.