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;
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
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
follow up
and changing
Row_Heights = '0,0,0,10,0,0,0'
autofit_height = 'no
changes nothing'
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
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
Cynthia--
Was this ever resolved? I'm having the same issue.
Thanks.
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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.