Proc SQL following Proc Import

[user24feb]

Hello,

I try to read a certain excel sheet, and after the import step proc sql is (implicitly) called. This step lasts extremely long, this is the computer breaks down. Please note that:

1) In principle proc import works. There are other functioning examples of XL-imports (on the very same computer).

2) If I use a libname-statement, it works, this is the excelfile seems allright.

--> this works:

LIBNAME myxls PCFILES PORT=9621 PATH="< ...>";

Data < ...>;
  Set myxls.'< ...>$'n;
Run;

--> this isn't working:

Proc Import Out=< ...>

            Datafile="< ...>"

  DBMS=ExcelCS

  Replace;

  Sheet="< ...>";

  SCANTEXT=YES;

  USEDATE=YES;

  SCANTIME=YES;

Run;

Could anybody please explain?

Thanks&kind regards

[ballardw]

Which isn't working the import or the proc sql?

If the import isn't working are you getting error messages, warnings or what is the issue?

If the question is about why the SQL runs long then details of that step.

And one potential problem that always lurks around Proc Import is that variable types may end up being something other than the SQL is expecting.

[user24feb]

The sql gets stuck. The import itself seems allright. The trouble is that the Enterprise Guide just seems to break down (same in SAS Base) and works on sql for an hour or longer if I let him. I neither get warnings nor errors (that is in fact the trouble). The excel sheet in question is nothing special, it only has a couple of lines and columns (<30).

In principle it is not a problem to use the libname. But I would like what is going on. I mean I think I could deal with an error message, but this behavior is strange.

Is there any possibility to influence import or the following sql?

Has anybody experienced a similar problem?

[Kurt_Bremser]

If other Excel's work, then it looks like a data problem.

Check the excel file/sheet in question. Jump to the last cell (there is an excel function for this). Somebody may have erroneously entered a blank in a cell, causing excel to think it has a sheet with 325687 rows and 417 columns, while you think it's just 20 rows with 15 columns. Or there is something that makes import think one of the columns is actually 10K bytes in size.

One thing that has happened to me in the past: doing an operation on a dataset where one of the columns was assigned an invalid format (that was not accessible because of a missing fmtsearch path) caused SQL to generate a giant utility file, take forever, and finally run out of resources (quota overrun) without any error message about the missing format(!). Reason #14 on my always growing list of "SAS SQL is evil, so don't use it".

[user24feb]

Thanks. But I'm afraid it still doesn't work.

I did replace the import with the libname statement, and maybe I try again next year.