BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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='type:String'} 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
12 REPLIES 12
Eric_SAS
SAS Employee
Try

style={tagattr='format:text'}

That should do what you want.
deleted_user
Not applicable
I was thinking I had tried that, but gave it another shot. It unfortunately does not do the trick. Any other ideas?
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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
genkiboy
Calcite | Level 5
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.
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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
data_null__
Jade | Level 19
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.
genkiboy
Calcite | Level 5
Thanks, Cynthia and data _null_. I updated my tagset, and now Cynthia's code gets me the desired results.
deleted_user
Not applicable
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
deleted_user
Not applicable
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.

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
  • 2094 views
  • 0 likes
  • 5 in conversation