BookmarkSubscribeRSS Feed
Shivi82
Quartz | Level 8

Hi,

I am trying to import an excel file with 65000+ observations and 20 variable however i am receiving an error message while importing the file in sas. Below is the error message and the code used for

importing the same:-

PROC IMPORT OUT= WORK.Trans

            DATAFILE= "F:\dell blue\Data Backup 05-02-2014\Downloads\SAS

Study Material\Statisitical Projects\Delay Transport Data\Delay_Status_

Data.xls"

            DBMS=EXCEL REPLACE;

     RANGE="Sheet1$";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

Error Mesage:-

WARNING: Failed to scan text length or time type for column ( same error message for multiple columns)

Kindly advice.

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

The range="Sheet1$" looks a bit odd.  what happens if you just put the proc import line and run (i.e. no other options).  Also as a suggestion convert to CSV.

Brent_PLNU
Calcite | Level 5

Instead of

RANGE="Sheet1$";

try

SHEET="'Sheet1$'";

Note the single quotes inside the double quotes.

Shivi82
Quartz | Level 8

No, its still gives an error.

ERROR: File _IMEX_.'Sheet1$'n.DATA does not exist.

ERROR: Import unsuccessful.  See SAS Log for details.

Malarkey
Obsidian | Level 7

I think you may have answered your own question...  'Sheet1$'n is, I think, they syntax you need to use.

Brent_PLNU
Calcite | Level 5

Is the excel tab name exactly 'Sheet1'? No leading or trailing spaces? (I ran into that once...)

You are getting a different error message, so it looks like progress. Now the error points to a mismatch between what you have in the SHEET statement and what is on your excel tab label.

I have code that I use everyday....

SHEET="'JRM Data$'";

the label on the excel tab is: JRM Data

Brent

Brent_PLNU
Calcite | Level 5

Oh, and one other option.

Run the import wizard, and utilize the last step where it saves the SAS code into a file. Then look at that code to see what it shows.

Brent

CHoens
Calcite | Level 5

Is there really an _error_ or only the  warning you mention?

What´s the exact problem? Are there really missing or wrong data?

Maybe the warning is only about an empty column.

kteague
Calcite | Level 5

I know this is old - but for anyone coming across this in 2017 (like I just did) - my issue was that my workbook was apparently protected. Once I unprotected the workbook, I no longer got this error message.

Kgomotso_Mokai
Calcite | Level 5

The file you are importing may be protected or in read only mode.

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
  • 9 replies
  • 10170 views
  • 1 like
  • 7 in conversation