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
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
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;
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.
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.
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.