topic Re: Preserving long numeric values in tagsets.excelxp generated spreadsheets in Microsoft Integration with SAS

Preserving long numeric values in tagsets.excelxp generated spreadsheets

BenConner — Fri, 12 Apr 2013 18:05:59 GMT

Hi,

Excel has an annoying habit of truncating a long character field when all the bytes in the field are numeric; it thinks it is a numeric value and truncates anything past 16 digits.

I have a 20 byte character key field that at times has an all-numeric key value. Anyone have a clever way to tell Excel to retain the character interpretation and not try to convert it to a number?

I was able to do this by enclosing the field in double quotes, but would like a cleaner solution if one is available.

Thanks!

--Ben

Re: Preserving long numeric values in tagsets.excelxp generated spreadsheets

data_null__ — Fri, 12 Apr 2013 19:16:21 GMT

Something like this maybe. untested.

define longnumber / display "Header text" style=[tagattr='format:Text'];

Re: Preserving long numeric values in tagsets.excelxp generated spreadsheets

BenConner — Fri, 12 Apr 2013 19:23:19 GMT

Will give that a shot. Thanks!

--Ben

Re: Preserving long numeric values in tagsets.excelxp generated spreadsheets

data_null__ — Fri, 12 Apr 2013 19:28:45 GMT

It should work that way but I have also created a style and defined a new style element

style data_Text from data / just=center tagattr='format:Text';

I think at one time the other way style override did not work but I can't remember.

This was created to display a format like 4. and keep the decimal. I think it will also work for big numbers. Hope so.

Message was edited by: data _null_

Re: Preserving long numeric values in tagsets.excelxp generated spreadsheets

BenConner — Fri, 12 Apr 2013 19:39:55 GMT

Outstanding! That worked like a charm. Thanks much!

--Ben