topic Re: Import numeric values with best32. format in SAS Programming

Import numeric values with best32. format

David_Billa — Sat, 04 Dec 2021 07:41:30 GMT

I'm trying to Import the following values via data step infile statement from .xls and I see that one of the value is rounded off and it should not be. Any help to resolve the issue?

0,051560936512756

0,213547098412685

Code which I used is,

(input(A,Best32.)) as values length = 8

Output which I got is,

0.0515609

0.213547098

Excepted Output:

0,051560937

0,213547098

Re: Import numeric values with best32. format

Kurt_Bremser — Sat, 04 Dec 2021 07:46:39 GMT

Use a format in your SQL:

(input(A,Best32.)) as values format=commax12.10

Untested, posted from my tablet.

Re: Import numeric values with best32. format

David_Billa — Sat, 04 Dec 2021 08:05:47 GMT

@Kurt_Bremser Thank you for your input. I just realized that we've used proc import to import data and then used data step to format the values. When I used commax12.10, I'm getting the values with correct number of decimal places but with 0s added to it at last instead of actual numbers. See below

0.0515609000

0.2135470980

Upon quick check, I see that proc import causing one of the value to trim as shown below. My question is why it is trimming only one of its value in proc import?

0.0515609

0.213547098

Re: Import numeric values with best32. format

Kurt_Bremser — Sat, 04 Dec 2021 08:58:26 GMT

The usual advice: do not use Excel files and PROC IMPORT. Save as csv, and read that with a data step where you have full control over informats and other variable attributes.

Re: Import numeric values with best32. format

Tom — Sat, 04 Dec 2021 17:43:56 GMT

@David_Billa wrote:

@Kurt_Bremser Thank you for your input. I just realized that we've used proc import to import data and then used data step to format the values. When I used commax12.10, I'm getting the values with correct number of decimal places but with 0s added to it at last instead of actual numbers. See below

0.0515609000

0.2135470980

Upon quick check, I see that proc import causing one of the value to trim as shown below. My question is why it is trimming only one of its value in proc import?

0.0515609

0.213547098

I don't see any examples of "trimmed" values in this post. Do you have some examples you can share?

If you have a numeric variable the way the number is printed (the FORMAT) does not have any impact on what is stored.

The INPUT() function operate on character strings. So if you have a numeric variable and try to use it as an argument to the INPUT() function then SAS will first convert the number to a character string using the BEST12. format. You should see notes in the SAS log about converting numbers to characters if you do that.

Also note that BEST is the name of a FORMAT. It is a format that will try how to "best" display the value of a number using a fixed number of characters. It does this by picking a number of decimal places that will fit and switching to scientific notation if needed. There is no corresponding concept of the "best" way to read text into a number, you just want to create the number that the text represents. So if you use BEST as an INFORMAT then SAS will just replace it with the normal numeric informat instead.

Re: Import numeric values with best32. format

ballardw — Sat, 04 Dec 2021 19:06:49 GMT

@David_Billa wrote:

@Kurt_Bremser Thank you for your input. I just realized that we've used proc import to import data and then used data step to format the values. When I used commax12.10, I'm getting the values with correct number of decimal places but with 0s added to it at last instead of actual numbers. See below

0.0515609000

0.2135470980

Upon quick check, I see that proc import causing one of the value to trim as shown below. My question is why it is trimming only one of its value in proc import?

0.0515609

0.213547098

Expected behavior for any fixed decimal format. ALWAYS consider format when looking at results.

Even when you don't assign a format SAS does and may result in appearance you don't expect.