The SAS Output Delivery System and reporting techniques

ExcelXP Tagset and sheet_name option

Reply
N/A
Posts: 0

ExcelXP Tagset and sheet_name option

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;
SAS Employee
Posts: 88

Re: ExcelXP Tagset and sheet_name option

Posted in reply to deleted_user
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/
N/A
Posts: 0

Re: ExcelXP Tagset and sheet_name option

Posted in reply to Chevell_sas
Nice. That might be my problem. Any ideas how I would go about updating/replacing the existing tagset with the new?
N/A
Posts: 0

Re: ExcelXP Tagset and sheet_name option

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: ExcelXP Tagset and sheet_name option

Posted in reply to Chevell_sas
Got it. That did it. Many thanks.
N/A
Posts: 0

Re: ExcelXP Tagset and sheet_name option

Posted in reply to deleted_user
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;
SAS Employee
Posts: 88

Re: ExcelXP Tagset and sheet_name option

Posted in reply to deleted_user
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;
N/A
Posts: 0

Re: ExcelXP Tagset and sheet_name option

Posted in reply to Chevell_sas
Tried your example and zero values are returned for both. I am however not running XP but rather 2003. Could this be the issue?
SAS Employee
Posts: 88

Re: ExcelXP Tagset and sheet_name option

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: ExcelXP Tagset and sheet_name option

Posted in reply to Chevell_sas
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?
Super Contributor
Posts: 260

Re: ExcelXP Tagset and sheet_name option

Posted in reply to deleted_user
> 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).
SAS Employee
Posts: 88

Re: ExcelXP Tagset and sheet_name option

Posted in reply to deleted_user
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;
SAS Employee
Posts: 88

Re: ExcelXP Tagset and sheet_name option

Posted in reply to deleted_user
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/
Ask a Question
Discussion stats
  • 12 replies
  • 329 views
  • 0 likes
  • 3 in conversation