BookmarkSubscribeRSS Feed
Dd07
Fluorite | Level 6

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;

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2939 views
  • 0 likes
  • 3 in conversation