Import only values from Excel worksheet.

[attjooo]

I am using SAS Enterprise Guide 6.1.

I want to use PROC IMPORT to import the values from an Excel workbook with multiple worksheets. It's 32-bit Excel 2010.

DBMS = Excel doesn't work, but DBMS =xlsx does. although the documentation says that the first alternative should be right. The second alternative I have found somewhere on the Internet.

Any comments  about the documentation on PROC IMPORT for SAS?

The worksheet have values in somecells and fomulas, giving values in other cells. I only want the first category of values and the second category of values resulting from the formulas.

So if A1 = 14 and B1 = 6 and C1 = A1 / B1, where the value in C1 is formatted with 1 decimal, then I want to get the values 14, 6 and 2.3 imported. Not 14, 6 and 2.333......333.

Any help would be appreciated.

[RichardinOz]

In your example 2.333......333 is the unformatted (underlying) value in Excel, which is what SAS reads and imports.  You can apply a SAS format such as 16.1 or comma20.1 after the import to achieve what you want.

The standard import procedure in SAS does not carry across the format applied in Excel, although in the case of dates SAS uses the Excel format to identify the value as a date, import it and adjust the base date and apply a SAS date format which might or might not look similar to the Excel format.

You might be able to achieve what you want by exporting the data as a text file (CSV or tab delimited) and importing that into SAS.

Richard