topic Re: ExcelXP Tagset and sheet_name option in ODS and Base Reporting

ExcelXP Tagset and sheet_name option

deleted_user — Wed, 27 Sep 2006 18:22:41 GMT

Anybody ever have problems with the XP tagset sheet_name option? When I use it, my worksheets are being named like so 'Table 1 - Date Set WORK.TEMP'. I believe this is the default, so for some reason this option is not begin picked up. Strangely, other tagset options are working (like autofilter = 'all'). My code is below. This is SAS on Unix by the way.

ods listing close;
ods tagsets.excelxp path='~/' file='temp.xls' style=minimal;
ods tagsets.excelxp options(sheet_name='DOESNTWORK');
proc print data=temp;
var _all_;
run;
ods tagset.excelxp close;

Re: ExcelXP Tagset and sheet_name option

Chevell_sas — Wed, 27 Sep 2006 20:53:32 GMT

Did you download the updated ExcelXP tagset from the below
web site, or are you running with the tagset which was shipped with the SAS 9.1?

http://support.sas.com/rnd/base/topics/odsmarkup/

Re: ExcelXP Tagset and sheet_name option

deleted_user — Wed, 27 Sep 2006 21:12:36 GMT

Nice. That might be my problem. Any ideas how I would go about updating/replacing the existing tagset with the new?

Re: ExcelXP Tagset and sheet_name option

deleted_user — Wed, 27 Sep 2006 21:19:37 GMT

Got it. That did it. Many thanks.

Re: ExcelXP Tagset and sheet_name option

deleted_user — Thu, 28 Sep 2006 02:15:10 GMT

So the sheet name option is working great but I'm seeing some formatting problems now. I tried using the following in line sytle overides and it transforms all values that were previoulsy alpha numberic (varible id example: "6455F3435") to O (zero). Same thing happens to my dollar amounts (varible amt). They turn to $0. Any ideas here?

*Output report;
ods listing close;
ods tagsets.excelxp path='~/' file='mod.xls' style=minimal;
ods tagsets.excelxp options(sheet_name='Temp' autofilter='all' frozen_headers='yes' width_fudge='0.7');
proc print data=temp label noobs;
var plan;
var id / style={tagattr='format:@'}; -Note also tried Text

var amt /style={tagattr='format:$#,##0_);[Red]$$#,##0$'};
run;
ods tagset.ExcelXP close;

Re: ExcelXP Tagset and sheet_name option

Chevell_sas — Thu, 28 Sep 2006 13:27:59 GMT

One thing that I spotted is that the opening ODS statement does not match the closing statement. However, after testing this with a single record, this appeared to works as expected. Does the below work for you? If this does not, you might want to open a tracking entry with Technical Support. We would want a copy of this .XLS file.

data one;
input id $ amt;
cards;
6455F3435 10000
;
run;

ods listing close;
ods tagsets.excelxp file="temp3.xls" style=minimal
options(sheet_name='Temp'
autofilter='all'
frozen_headers='yes'
width_fudge='0.7');
proc print data=one label noobs;
var id / style={tagattr='format:@'};
var amt /
style={tagattr='format:$#,##0_);[Red]$$#,##0$'};
run;
ods tagsets.excelXP close;

Re: ExcelXP Tagset and sheet_name option

deleted_user — Thu, 28 Sep 2006 14:02:05 GMT

Tried your example and zero values are returned for both. I am however not running XP but rather 2003. Could this be the issue?

Re: ExcelXP Tagset and sheet_name option

Chevell_sas — Thu, 28 Sep 2006 16:33:28 GMT

I don't think the version of Excel is the problem here. Please go ahead and open a tracking entry with Technical Support using the below URL. Be sure to include the Version of SAS to include the service pack and send a copy of this .XLS file which was generated.

http://support.sas.com/techsup/contact/submit_emits2.html

Re: ExcelXP Tagset and sheet_name option

deleted_user — Thu, 28 Sep 2006 17:58:39 GMT

Will do. I reverted to ods html file=temp.xls for my product. It's less elegant but does the job. Any ideas how to prevent long numbers from being displayed in scientific notation within excel?

Re: ExcelXP Tagset and sheet_name option

Olivier — Fri, 29 Sep 2006 12:42:52 GMT

> Any ideas how to prevent long numbers from being displayed in scientific notation within excel?

Why not just widen columns in Excel manually ? That should display numbers in a normal way (hope so).

Re: ExcelXP Tagset and sheet_name option

Chevell_sas — Fri, 29 Sep 2006 13:30:08 GMT

You can use the Excel CSS style property mso-number-format using the text format as the argument which will display the full value. You can do this with the MSOffice2k tagset and the HTML destination.

data one;
x="123456789101112";
run;

ods html file="temp7.xls";

proc print data=one;
var x / style(data)={htmlstyle="mso-number-format:\@"};
run;

ods html close;

Re: ExcelXP Tagset and sheet_name option

deleted_user — Tue, 17 Oct 2006 20:28:34 GMT

Hi,

I seem to have to same problem: the sheet_name doesn't change the tab name.

So how did you update/replace your existing tagset with the updated one??

Thanks in advance.

Re: ExcelXP Tagset and sheet_name option

Chevell_sas — Thu, 19 Oct 2006 17:13:47 GMT

You can download the updated ExcelXP tagset from the below location. You can then either bring this file into the program editor and submit it, or %INCLUDE this file. By default, the updated tagset will be written to the SASUSER location which is where we look for the tagset first.

%include "excltags.tpl";

http://support.sas.com/rnd/base/topics/odsmarkup/