topic Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits in ODS and Base Reporting

ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

Inp — Sat, 17 Jul 2021 13:46:37 GMT

I am trying to loading 17 digits length number from sas table to ODS TAGSETS.EXCELXP ( using SAS94) . but it is messing up the last 3 digits of the number. Can any one please let me know the solution to my problem. Here is my sample coding .

DATA TEMP1;
X = 12345678901234567;
RUN;

FILENAME TEMP 'C:\TMP\TEST.XLS';

ODS _ALL_ CLOSE;
ODS TAGSETS.EXCELXP FILE=TEMP STYLE=STYLES.HTMLBLUE RS=NONE;
ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="MATCH"

EMBEDDED_TITLES='YES'
AUTOFILTER='ALL' AUTOFIT_HEIGHT='YES' MINIMIZE_STYLE='YES'
ABSOLUTE_COLUMN_WIDTH='30'
ORIENTATION='LANDSCAPE' SCALE='75');

RUN;

TITLE 'TESTING AS OF MAY2021';

PROC REPORT DATA=TEMP1 NOWD SPLIT='*' ;
COLUMN X;
DEFINE X / 'ACCOUNT'
style={tagattr='format:00000000000000000'};
RUN;

ODS _ALL_ CLOSE;
ODS LISTING;

Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

Kurt_Bremser — Sat, 17 Jul 2021 16:55:51 GMT

You run into the limits of numeric precision caused by the finite amount of binary digits used to store decimal numbers. Because of this, SAS (and any software that uses 64 bits to store numbers in floating point format) can "only" handle up to 15 (and for some numbers, 16) decimal digits without loss of precision.

For the field of statistics, this poses no problem.

If that "number" is in fact some kind of code, store it as character.

Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

Inp — Sat, 17 Jul 2021 17:32:49 GMT

Thanks very much for your help,

Even I changed this X value to y as charactor , but it doesn't shows as charactor and truncate as numeric value. Is there any way to change this y to charactor where I can load to ODS TAGSETS.EXCELXP. Should I have to put any style where it can change as Charactor? . ? If any ,can you please tell how?

DATA TEMP1;
length y $16;

X = 12345678901234567;

y=put(x,z16.);

RUN;

FILENAME TEMP 'C:\TMP\TEST.XLS';

ODS _ALL_ CLOSE;
ODS TAGSETS.EXCELXP FILE=TEMP STYLE=STYLES.HTMLBLUE RS=NONE;
ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="MATCH"

EMBEDDED_TITLES='YES'
AUTOFILTER='ALL' AUTOFIT_HEIGHT='YES' MINIMIZE_STYLE='YES'
ABSOLUTE_COLUMN_WIDTH='30'
ORIENTATION='LANDSCAPE' SCALE='75');

RUN;

TITLE 'TESTING AS OF MAY2021';

PROC REPORT DATA=TEMP1 NOWD SPLIT='*' ;
COLUMN y;
DEFINE y / 'ACCOUNT' format=$16.;

RUN;

ODS _ALL_ CLOSE;
ODS LISTING;`

Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

Kurt_Bremser — Sat, 17 Jul 2021 18:04:24 GMT

The loss of precision happens here:

X = 12345678901234567;

Anytime you cram such a number into a SAS variable, it happens.

Store it as a string in the first place:

X = "12345678901234567";

Re: ODS TAGSETS.EXCELXP Loading 17 digits length number , truncating or change the last 3 digits

Inp — Sun, 18 Jul 2021 01:42:55 GMT

Thanks very much . That resolved my problem.

Thanks and Regards,

Inp