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

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 2956 views
  • 0 likes
  • 3 in conversation