Import 2010 Excel Files using GuessingRows

Question

I have spreadsheets with columns of mixed data types ie:

Column1
13450
67/89
823ad

I want SAS to read all entries so it discovers the data is mixed and so imports it all as character format (otherwise it can define as numeric and so creates missing entries for non-numeric values). So I need to use the 'GUESSINGROWS' feature of PROC IMPORT.

I was able to do this with .XLS files as so:

PROC IMPORT OUT=importeddata
            DATAFILE = "C:\User\Example\Excel File.xls"
            DBMS=XLS REPLACE;
       SHEET='Input';
       GETNAMES=YES;
       MIXED=YES;
       GUESSINGROWS=32767;
RUN;

However, these files are now being saved as .XLSM files and so I need to use the new DBMS of EXCEL. When doing this, GUESSINGROWS is no longer a valid statement.

Does anyone know how I can use the GUESSINGROWS statement while importing from a .xlsm file? Or another way to define the input variable format when importing from .xlsm?

Use libref method and dbsatype to specify it specifically. What DBMS are you using? — Reeza
I know, I meant to type dbsastype, that allows you to explicitly cast a column in the lib ref method, since his question asks if there's another way. — Reeza

Reeza Reeza · Accepted Answer · 2014-10-07T19:20:27

https://communities.sas.com/message/193134#193134

The response from Art on Jan 8, 2014 is what you're looking for, change your type to CHAR instead of Date and the column name to the correct column name.

Import 2010 Excel Files using GuessingRows

1 Answers