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

[Clark_]

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"

[SASKiwi]

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

[Clark_]

I am importing via Import Data menu

[FreelanceReinh · Accepted solution]

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).

[Clark_]

That looks to have worked, thanks!