Help using Base SAS procedures

SAS ACCESS to OLE DB

Reply
Super Contributor
Super Contributor
Posts: 444

SAS ACCESS to OLE DB


hi guys,

trying to access excel through ole db as i dont have the access to pc files

  libname test oledb provider="ORAOLEDB.ORACLE.1"

  properties=("data source"='C:\Users\xy\Desktop\zd.xls')

    provider_string=" Excel 8.0";

the error message is : tns:could not resolve the connect specifier specified

Any idea  what should be changed here?

Super Contributor
Posts: 578

Re: SAS ACCESS to OLE DB

Your'e using an Oracle OLEDB provider.  It connects to oracle databases.  I think the Microsoft Jet driver can connect to an excel file.

Super Contributor
Super Contributor
Posts: 444

Re: SAS ACCESS to OLE DB

i actually found lot of articles on google with the above code saying that it should connect me to excel but nothing i tried so far is working

Respected Advisor
Posts: 4,934

Re: SAS ACCESS to OLE DB

It works for me with this syntax:

libname db OLEDB INIT_STRING=

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\xy\Desktop\zd.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';Persist Security Info=True";

PG

PG
Respected Advisor
Posts: 4,934

Re: SAS ACCESS to OLE DB

You can read more formats (including .XLSX) with this :

libname db OLEDB INIT_STRING=

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\xy\Desktop\zd.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';Persist Security Info=True";

PG

PG
Super Contributor
Super Contributor
Posts: 444

Re: SAS ACCESS to OLE DB

will try tomorrow.

Thanks

have you guys also tried to connect through SAS to more schemas in Oracle and access the tables/libraries at the same time?

I tried doing so today and i can only access the tables of one schema at a  time.

Any thoughts on this to share with me please?

Super User
Posts: 19,869

Re: SAS ACCESS to OLE DB

You need a dsn entry for each schema or different libnames for each schema.

Super Contributor
Super Contributor
Posts: 444

Re: SAS ACCESS to OLE DB

yea,you are right Smiley Happy

Thanks Reeza

Super Contributor
Super Contributor
Posts: 444

Re: SAS ACCESS to OLE DB

hey PG

so i copied your syntax  but then when i try to ACCESS  the file through this library i get errors

libname db OLEDB INIT_STRING="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\xx\Desktop\yyy.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';Persist Security Info=True";

proc print data=db.yyy;run;

ERROR: Invalid data set name db.daily.report_jan2_8.


ERROR: File WORK.DAILY.DATA does not exist.

ps.so i think the " db.yyy" was wrong and  i did try to access any of the sheets in this excel file

db.'sheet1$'n and it did not work again

Error:

ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : The Microsoft Access database

       engine could not find the object 'sheet1$'. Make sure the object exists and

       that you spell its name and the path name correctly. If 'sheet1$' is not a

       local object, check your network connection or contact the server administrator.

What i am doing wrong now ? Smiley Happy

Respected Advisor
Posts: 4,934

Re: SAS ACCESS to OLE DB

I can see the sheets in SAS Explorer and a command like:

proc print data=db.'Sheet1$'n; run;

gives the expected result. I wonder why the error message you got refers to Microsoft Access database...

PG

PG
Super Contributor
Super Contributor
Posts: 444

Re: SAS ACCESS to OLE DB

my windows is 64bit.that should not be the  problem,huh?

Ask a Question
Discussion stats
  • 10 replies
  • 613 views
  • 1 like
  • 4 in conversation