turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- SAS importing and rounding

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-09-2009 11:19 AM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to WLP165

06-09-2009 02:10 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to WLP165

06-10-2009 12:59 PM

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

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

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to LinusH

06-10-2009 02:24 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to WLP165

06-10-2009 01:27 PM

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.

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.