topic Re: Proc sql pass through with excel in SAS Programming

Proc sql pass through with excel

Jude815 — Tue, 25 Mar 2014 18:17:29 GMT

I'm trying to use proc sql pass through with excel. I have had the same code and used it for awhile, but now I am getting an error that states the microsoft access engine could not find the object. Any suggestions on how to fix this problem?

Re: Proc sql pass through with excel

Reeza — Tue, 25 Mar 2014 19:28:09 GMT

Have you changed your version of Excel or your ODBC drivers? Or your OS? A lot of moving parts in such a system.

Always helps to post your code as well.

Re: Proc sql pass through with excel

Jude815 — Tue, 25 Mar 2014 19:53:33 GMT

No changes. Here is a copy of the code and error:

proc sql dquote=ansi;

connect to excel (header=yes path="&filepath.\&filename.");

create table jude.sampling as

select * from connection to excel

(select * from strata);

ERROR: Describe: The Microsoft Access database engine could not find the object 'strata'.

Make sure the object exists and that you spell its name and the path name correctly. If

'strata' is not a local object, check your network connection or contact the server administrator.

As you can see, I am not trying to use access but excel. The name and path are correct.

Re: Proc sql pass through with excel

Reeza — Tue, 25 Mar 2014 20:18:24 GMT

Is Strata a sheet name or a named range?

Also, does the case match, ie strata vs Strata

Re: Proc sql pass through with excel

PGStats — Tue, 25 Mar 2014 20:43:13 GMT

If your file is .xlsx, try to convert it to older format .xls and see if you can access that.

Re: Proc sql pass through with excel

Jude815 — Tue, 25 Mar 2014 20:52:26 GMT

Thanks all, I found I had to put my worksheet name in [strata$] and that worked.

Re: Proc sql pass through with excel

Jude815 — Tue, 25 Mar 2014 20:53:28 GMT

Reeza wrote:

Is Strata a sheet name or a named range?

Also, does the case match, ie strata vs Strata

Strata was a sheet name and they were the same.

Next time I will try to be more clear from the beginning.