topic Re: Number values ending in ".00" being rounded incorrectly when imported from Excel workb in SAS Enterprise Guide

Number values ending in ".00" being rounded incorrectly when imported from Excel workbook

Clark_ — Wed, 01 Mar 2023 17:56:56 GMT

SAS EG Version: 7.15 (7.100.5.5850) (64-bit)

I am importing data from an Excel workbook. One of my data columns is "Cost" (spend in USD). The column in Excel is formatted as Number. When importing this data into SAS the are usually no issues. However, if the "Cost" has zero cents, i.e. the value ends with ".00" (e.g. 1760.00), then SAS is dividing the number by 100/shifting the decimal place over 2 points. E.g. 1760.00 becomes 17.60

The Source Informat and Output Format/Output Informat are BEST12.

Can anyone assist in preventing this decimal shift from happening? I would just like to keep the imported number values as "XXXX.00"

Re: Number values ending in ".00" being rounded incorrectly when imported from Excel workb

SASKiwi — Wed, 01 Mar 2023 19:12:18 GMT

How are you importing in EG? Via the Import Data menu or using code?

Re: Number values ending in ".00" being rounded incorrectly when imported from Excel workb

Clark_ — Wed, 01 Mar 2023 19:22:47 GMT

I am importing via Import Data menu

Re: Number values ending in ".00" being rounded incorrectly when imported from Excel workb

FreelanceReinh — Wed, 01 Mar 2023 20:07:51 GMT

Hello @Clark_ and welcome to the SAS Support Communities!

Your screenshot says that the "Source Informat" used for variable Cost was BEST12.2. And this decimal specification "2" is the issue: It instructs SAS to assume that the last two digits of a number being read are decimals if there is no decimal point. So, remove that "2" from the informat.

Here's a SAS log showing again what happened:

143   data _null_;
144   wrong=input('1760', 12.2);
145   put wrong= 12.2;
146   right=input('1760', 12.);
147   put right= 12.2;
148   run;

wrong=17.60
right=1760.00

I used informats 12.2 and 12. as these are identical to the corresponding "BEST..." informats. Note that the 12.2 format in the PUT statements does use the decimal specification because the BEST12. format would not show decimals for integer values or trailing zeros as in 17.60 (and it would ignore any decimal specification like "BEST12.2").

Edit:

@Clark_ wrote:
I am importing via Import Data menu

You can change the source informat in step "3 of 4 Define Field Attributes" [or similar; I don't have Excel on my workstation, tested it with a text file instead] of the import process (double-click the informat in question).

Re: Number values ending in ".00" being rounded incorrectly when imported from Excel workb

Clark_ — Wed, 01 Mar 2023 20:40:42 GMT

That looks to have worked, thanks!