Proc export with guessingrows

[Dd07]

Hello All,

I am trying to import data from excel (I can not use csv) however guessing rows i want to fix as 10000

I believe i can not use guessing rows as DBMS EXCEL.

Please suggest .

Thanks a lot .

==================================================

PROC IMPORT OUT= AMPO.&out.
DATAFILE= "&path.&Source."

DBMS=EXCEL REPLACE;

RANGE="&sheet.$" ;
GETNAMES=YES ;

MIXED=Yes;

Guessingrows=1000;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;

RUN;

[RW9]

Hi,

Tip, avoid coding all in upper case, it makes code hard to read.  You are quite right that guessingrows only applies to delimited files:

http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#p13kvtl8ezj13in17i6m9...

The guessing rows for Excel is defined in a registry entry - this is the library of system options and such like for your OS - so you would need your IT group to change it.

Why can you not use CSV?  Just save the file from Excel and choose file type CSV, save it somewhere then write a datastep import program for it.  That would always be my preferred option.  The second question I had was why would you specify the guessingrows as 1000, its not clear why the decision on that.  You can force certain formats by adding a row to excel at the top which is of the type and length you require.

[Reeza]

If you have a later version of SAS try DBMS=XLSX

However there's no absolute way to specify the variable type while using Excel so you may need to implement fixes after the initial import.