The SAS Output Delivery System and reporting techniques

ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Reply
N/A
Posts: 0

ODS ExcelXP tagset - writing out a text field that is sometimes numeric

I have a field in my dataset that is 8 characters long. Sometimes, all of the characters are numeric and in fact, the first observation in the dataset is 00000001. I use style={tagattr='typeSmiley Frustratedtring'} to force the field to be text in Excel, but it still seems to be getting picked up as numeric.

The worst part is that I try a small test program and it works ok. I do a proc contents on the dataset that doesn't work and on the one that works in my test program and both are type char and len 8. So I don't think there's a difference there.

Does anyone have any thoughts on a workaround or a way for me to diagnose or do it differently? I am an XML novice, no doubt.

Thanks,
--Charlie
SAS Employee
Posts: 95

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Try

style={tagattr='format:text'}

That should do what you want.
N/A
Posts: 0

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

I was thinking I had tried that, but gave it another shot. It unfortunately does not do the trick. Any other ideas?
SAS Super FREQ
Posts: 8,745

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Hi:
When I try this code:
[pre]
data charstr;
infile datalines;
input name $ charstr $ state $;
format name charstr $8. state $2.;
return;
datalines;
Alan 0000001 OH
Bob 0000002 AL
Carl 0000003 NC
;
run;

proc contents data=charstr;
run;

ods tagsets.excelxp file='c:\temp\charstr.xls'
style=sasweb;
proc print data=charstr;
var name;
var charstr /
style(data)={tagattr="Format:text"};
var state;
run;
ods _all_ close;

[/pre]

and open the resulting Spreadsheet Markup Language file with Excel, my number is stored as text. Excel (in Office 2007) shows me that the number is stored as text with a little green triangle in the upper left hand corner of the cell. Excel is not happy about storing a number as text.

If I change the DATALINES to this:
[pre]
datalines;
Alan 0000001 OH
Bob 0000002 AL
Carl 0000003 NC
Dave Nothing GA
;
[/pre]

Then Excel does NOT put the green triangle in the cell for the string "Nothing", but still does put the green triangle (warning of number stored as text) for the first 3 observations.

If by "not work" you mean that you're getting the green triangle, this is the expected behavior. As far as I know, you have to manually "clear" that triangle by selecting a drop down box and choosing "Ignore Error" on the cells with the green triangle.

If by "not work" you mean that you're getting the numbers without leading zeroes, as numbers in the Excel worksheet, then you might consider working with Tech Support on this issue.

cynthia
N/A
Posts: 0

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Thanks Cynthia. By "not works", I mean that it trims the leading zeros, not the green triangle thing. I'll talk to Tech Support.

--Charlie
N/A
Posts: 0

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

OK, I got it. I was also overriding the width of the column with:

style(data)={cellwidth=250}

For some reason, it doesn't like that. When I remove that, it works. I tried out your (Cynthia's) test code and it worked for me, so was able to back into the diagnosis.

Thanks for your help.

--Charlie
Occasional Contributor
Posts: 5

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

I'm not going to belabor this because I don't have a business need for it at the moment, but running Cynthia's code as-is I get numbers in my Excel spreadsheet. I'm using Excel 2003 and SAS v 9.1.3 in case that matters.
N/A
Posts: 0

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Yes, that was my last post. I get it to work as well with Cynthia's code. That helped me figure out that the problem has something to do with setting the width of the column. When I put that override on, it doesn't work, but I'm ok with taking it off. So thanks.
SAS Super FREQ
Posts: 8,745

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Hi:
I no longer have Excel 2003 to test with. All my computers have Excel 2007. It's possible this is an issue with 2003 vs 2007. (Most likely)

If and when you do have a need for this, you should work with Tech Support if my code does not give you the results you expect (number as text). As far as I know, they could test in Excel 2003 for you and help pinpoint the likely cause.

cynthia
Respected Advisor
Posts: 3,777

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

I have EXECL 2003 and get the proper result with when using the updated TAGSET.

With this really old verion NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.28, 08/29/05). TAGATTR seems to be ignored.

Upgrading to NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08). I get the correct result.
Occasional Contributor
Posts: 5

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Thanks, Cynthia and data _null_. I updated my tagset, and now Cynthia's code gets me the desired results.
N/A
Posts: 0

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Charlieh,

I have run into this problem frequently with 10 digit account numbers that often begin with leading zeroes. My solution is by no means elegant, but it works. I simply create a new variable with an apostrophe in the first position followed by the account number. That forces Excel to treat it as the character value it is, and the leading zeroes are retained.

HTH
Paul
N/A
Posts: 0

Re: ODS ExcelXP tagset - writing out a text field that is sometimes numeric

Thank you. This issue has been resolved. It had to do with my overriding the cellwidth. When I remove that override, Cynthia's sample code works just fine. So does my final code.

As to putting the apostrophe on the end, that's exactly where I first went but didn't like it as a long-term solution.
Ask a Question
Discussion stats
  • 12 replies
  • 424 views
  • 0 likes
  • 5 in conversation