topic Re: Issue with Titles in Excel in ODS and Base Reporting

Issue with Titles in Excel

Vish33 — Thu, 22 Nov 2012 09:43:38 GMT

Hi All,

I have an issue with titles in the report getting truncated when using ODS Tagsets.ExcelXP . when i reduce the table column width the titles are getting overrided to this width, but i want to display the titles irrespective of the report column width. can anyone suggest on this and even the data color is getting overrided to blue color which i have not given.

Please look into the sample code below and suggest on this

title;

footnote;

ods listing close;

ods escapechar="^";

ods tagsets.excelxp

file="/location/myfile.xls"

style=BarrettsBlue

OPTIONS ( Sheet_Name = 'Program'

Sheet_Interval = 'proc'

Orientation = 'portrait'

FitToPage = 'yes'

Pages_FitWidth = '1'

Pages_FitHeight = '100'

Center_Horizontal = 'yes'

Embedded_Titles = 'yes'

Embedded_Footnotes = 'yes'

Absolute_Column_Width = '30,25'

);

/* title1*/

title1 j=left bold font=arial bc=cxffffff color=cx685c53 h=18pt "XYZ Corporation INDIA Pvt ltd (Monthly Standard report)";

title2 j=left bold font=arial bc=cxffffff color=cx685c53 h=10pt "Monthly Program Report";

title3 j=left font=arial bc=cxffffff color=cx685c53 h=10pt "Activity From: &startDatePrvYr through &endDatePrvYr";

proc report data=final

style(report) = [just=left backgroundcolor=cxffffff BORDERRIGHTCOLOR=white BORDERTOPCOLOR=white BORDERCOLOR=white font = (Arial,10pt)] split= "*"

style(header)=[just=left BACKGROUND =cx685c53 BORDERRIGHTCOLOR=white BORDERTOPCOLOR=white foreground=white BORDERCOLOR=white font = (Arial,10pt) font_weight = bold]

style(column)=[BORDERRIGHTCOLOR=white foreground=cx685c53 BORDERTOPCOLOR=white BORDERCOLOR=white font = (Arial,10pt)];

column program Count ;

define program / display "Program" style(column)=[ indent=4 tagattr="format:@" just=center background=cxffffff foreground=cx685c53]

style(header)=[just=left indent=2 ];

define Count / analysis "Count" style(column)=[ just=center background=cxffffff foreground=cx685c53]

style(header)=[just=left indent=2 ];

rbreak after / summarize ;

compute after;

program='Total';

call define(_row_,'style',"style=[font_weight=bold]");

endcomp;

run;

ods tagsets.excelxp close;

ods _all_ close;

ods listing;

here title1 is getting truncated to the column width 15 as given in absolute column width, but i want to remain the same width for column and titles should display as they are. And the color for data i given as "cx685c53", but its getting overrided by blue color.

Thanks,

Vish

Re: Issue with Titles in Excel

Cynthia_sas — Thu, 22 Nov 2012 16:34:30 GMT

Hi:

See the attached screenshot. In SAS 9.3, with Excel 2010 opening the file, I don't have an issue with the TITLE width using my version of SAS to create the XML and Excel 2010 to render the XML into spreadsheet form. The title seems OK to me -- no truncation. I am running the version v1.127, 09/26/2011 of TAGSETS.EXCELXP.

I do see a color difference. I have come to expect that with Excel. If it doesn't like a color that you've specified, it will map the color to something else. It was worse before 2010, when Excel only had a color palette of 56 colors. But I notice that it still happens. If you look inside the generated XML with Notepad or TextPad, you can verify that SAS sent the hex code color CX685c53. But SAS is not rendering the color. SAS is sending the color to Excel. Excel is responsible for rendering the color. I've attached a screen shot of the title color in Excel and you can see that Excel did not map that hex color correctly. I looked in the XML and SAS sent "685c53" for the title color. So, Excel is being obstinate, here.

I would recommend that you play around with shades of gray until you find one that Excel will render accurately.

If you continue to have issues with this, then I would recommend that you open a ticket with Tech Support. First, I'd double check your version of TAGSETS.EXCELXP and make sure that you are using the most recent version of the destination tagset template.

http://support.sas.com/kb/32/394.html

cynthia

Re: Issue with Titles in Excel

art297 — Thu, 22 Nov 2012 16:41:19 GMT

: Would you mind posting the code that you ran? I had attempted the same test, on 9.3, but ended up with an xml file that Excel couldn't read.

Re: Issue with Titles in Excel

Cynthia_sas — Thu, 22 Nov 2012 18:52:28 GMT

Hi, Art:

Sure, here it is. I made some data from SASHELP.CLASS and tidied up the code indent levels. The only thing I really changed was that I hardcoded dates instead of macro variables in TITLE3 and I changed the FILE= location.

cynthia

title;
footnote;
ods listing close;
ods escapechar="^";

data final;
   set sashelp.class;
   where age le 13;
   program = name;
   count=age;
run;

ods tagsets.excelxp
   file="c:\temp\myfile.xls"
   style=BarrettsBlue
   OPTIONS ( Sheet_Name = 'Program'
             Sheet_Interval = 'proc'
             Orientation = 'portrait'
             FitToPage = 'yes'
             Pages_FitWidth = '1'
             Pages_FitHeight = '100'
             Center_Horizontal = 'yes'
             Embedded_Titles = 'yes'
             Embedded_Footnotes = 'yes'
             Absolute_Column_Width = '30,25');

title1 j=left bold font=arial bc=cxffffff color=cx685c53 h=18pt
"XYZ Corporation INDIA Pvt ltd (Monthly Standard report)";
title2 j=left bold font=arial bc=cxffffff color=cx685c53 h=10pt
"Monthly Program Report";
title3 j=left font=arial bc=cxffffff color=cx685c53 h=10pt
"Activity From: 2011 through 2012";

proc report data=final nowd split= "*"
style(report) = [just=left backgroundcolor=cxffffff
      BORDERRIGHTCOLOR=white BORDERTOPCOLOR=white
      BORDERCOLOR=white font = (Arial,10pt)]
style(header)=[just=left BACKGROUND =cx685c53
       BORDERRIGHTCOLOR=white BORDERTOPCOLOR=white
       foreground=white BORDERCOLOR=white
       font = (Arial,10pt) font_weight = bold]
style(column)=[BORDERRIGHTCOLOR=white
       foreground=cx685c53 BORDERTOPCOLOR=white
       BORDERCOLOR=white font = (Arial,10pt)];
column program Count ;
define program / display "Program"
        style(column)=[ indent=4 tagattr="format:@"
              just=center background=cxffffff foreground=cx685c53]
        style(header)=[just=left indent=2 ];
define Count / analysis "Count"
       style(column)=[ just=center background=cxffffff
             foreground=cx685c53]
       style(header)=[just=left indent=2 ];
rbreak after      / summarize ;
compute after;
   program='Total';
   call define(_row_,'style',"style=[font_weight=bold]");
endcomp;
run;
ods tagsets.excelxp close;
ods _all_ close;
ods listing;

Re: Issue with Titles in Excel

art297 — Thu, 22 Nov 2012 19:45:44 GMT

Cynthia: Very much appreciated! You used the same method to test as I did, but your code actually produces an xml file that I could open.

As for the OP, the only thing I can think of is that he is somehow introducing a space into column b. That would produce the kind of appearance he was referring to.

Re: Issue with Titles in Excel

Vish33 — Thu, 29 Nov 2012 07:52:16 GMT

Thanks Cynthia and Art,

I am using EG 4.2 version and Unix environment. I see the attached files with titles not getting split, even i am getting the same when Absolute_Column_Width = '30,25', but i dont want to display like that as it looks more spacious, when i tried giving Absolute_Column_Width = '15,10' then the titles are getting split, which i dont want to happen, is there any way that this column width doesnt overrides the titles width.

And the colors also am getting like the sample you have provided, i am unable to understand why the color is getting overrided when it is coming for the Header part(cx685c53). The same color we given for titles and data part, can you suggest on this.

Re: Issue with Titles in Excel

art297 — Thu, 29 Nov 2012 14:40:03 GMT

: I didn't see any attachment.

Re: Issue with Titles in Excel

Cynthia_sas — Thu, 29 Nov 2012 23:01:30 GMT

Hi:

As I explained, Excel is messing up the color rendering. SAS is sending the color code. Excel is rendering it incorrectly. I suspect, but it's only a hunch, that somehow Excel is interpreting the colors wrong. I don't know anything to do about it except find a different shade of gray. For example, if I use cx686868 as the color to send, Excel seems to not tamper with the color.

As for the title, when I use an ABSOLUTE_COLUMN_WIDTH="15,10", I did not get any title truncation. See my screenshot.

I'd recommend that you open a track with Tech Support on this.

cynthia