topic Re: Proc export with guessingrows in SAS Procedures

Proc export with guessingrows

Dd07 — Thu, 09 Jun 2016 08:21:57 GMT

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;

Re: Proc export with guessingrows

RW9 — Thu, 09 Jun 2016 08:26:47 GMT

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#p13kvtl8ezj13in17i6m99jypcwi.htm

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.

Re: Proc export with guessingrows

Reeza — Thu, 09 Jun 2016 09:48:52 GMT

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.