SAS importing and rounding

[WLP165]

Hi,

I use SAS for Windows v9.1.3 SP4 and am currently a novice-level user of SAS.

Issue - Using Proc SQL (and options getnames=yes and mixed=yes), I import an Excel worksheet into a SAS dataset. The last two columns in the Excel worksheet contain numbers expressed to two decimal places x.xx. They originally are expressed out to 14 decimal places, but are formatted in Excel 2003 to two decimal places using Format, Cells, Number. A Visual Basic program was used to calculate and generate these two columns of numbers for display in the worksheet.

After importing the worksheet data, I've noticed small differences in some of the decimal numbers in the worksheet when compared to the same in the SAS dataset. For example, 1.39 in the worksheet appears as 1.40 in SAS, 0.51 as 0.52, 2.99 as 3.00, 1.95 as 1.96. I'm guessing that when I run a Proc Compare, these differences will also be displayed.

Question - Is SAS employing a "default" numerical rounding method when importing Excel worksheet data into a SAS dataset? If so, what is it?

[WLP165]

From WLP165: Inserting one minor clarification. The last sentence of the first paragraph is probably better worded as "An underlying Visual Basic program was used to initially calculate and generate these two columns of data."

[LinusH]

Are you using proc import or libname excel?
PROC COMPARE can't compare directly with the Excel spread-sheet, unless accessed via libname engine.
I don't think SQL itself would round any numbers unless you specified so in the code... What format is used in the corresponding SAS table?

/Linus

[WLP165]

I have not used "libname excel" as of yet. I'll give it a try.

I've used Proc Import with DBMS=XLS and getnames=yes. After importing the Excel worksheet, the resulting format for the two columns in the SAS dataset is BEST11 (x.xxxxxxxxx, xx.xxxxxxxx). The same two columns in the Excel worksheet are in Number format. The first 23 observations of the SAS dataset contain a period in the two variables which is expected (blanks are present in these same cells of the input Excel worksheet).

I've also used Proc SQL with getnames=yes and mixed=yes (the latter is the only way I can get any numbers in at all when keeping the Excel file closed). After importing the same Excel worksheet, the resulting format for the two columns in the SAS dataset is $255 (character type, 255 positions).

WLP165

[deleted_user]

WLP165
There are quite a few comments about rounding numbers in this blog post. http://blogs.sas.com/supportnews/index.php?/archives/46-Numbers-and-Magic-Tricks.html#comments
You may find it interesting.