BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
12 REPLIES 12
Chevell_sas
SAS Employee
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/
deleted_user
Not applicable
Nice. That might be my problem. Any ideas how I would go about updating/replacing the existing tagset with the new?
deleted_user
Not applicable
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.
deleted_user
Not applicable
Got it. That did it. Many thanks.
deleted_user
Not applicable
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;
Chevell_sas
SAS Employee
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;
deleted_user
Not applicable
Tried your example and zero values are returned for both. I am however not running XP but rather 2003. Could this be the issue?
Chevell_sas
SAS Employee
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
deleted_user
Not applicable
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?
Olivier
Pyrite | Level 9
> 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).
Chevell_sas
SAS Employee
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;
Chevell_sas
SAS Employee
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/

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1544 views
  • 0 likes
  • 3 in conversation