SAS libname odbc, how to access a two-level name table

5
votes

I recently found the libname statement is very handy to access tables reside in odbc. But I don't know how to access those tables having a two-level names. Below is an example.

Old code I use (this code works):

proc sql;
    connect to odbc(dsn=edw_nz user=&username. pw=&password.);

    create table test select * from connection to odbc(
        select  *
        from EDW_XYZ_PROD01..Table_xyz);
quit;

Now, I want to use the libname facility to access the odbc:

libname edw odbc database=edw_nz user=&username. pw=&password.;
proc sql;
    create table test as
        select *
        from edw.EDW_XYZ_PROD01..Table_xyz;
quit;

I got this error: ERROR 22-322: Syntax error, expecting one of the following: a name, (, ), ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, OUTER, RIGHT, UNION, WHERE. ERROR 200-322: The symbol is not recognized and will be ignored.

Any one can help? Thanks a lot!

3
odbcsas

3 Answers

5
votes

SAS cannot handle 3 level names.

You need to specify the schema/database inside the libname section. You have a few options (read the doc for all the options).

We use ODBC to connect to our SQL server instances like this:

libname pdata odbc complete='DSN=SQLServerProd;Database=MyDatabase';

The complete= option allows you specify the full ODBC connection string. This should allow you to specify the database/schema.

1
votes

Is EDW_XYZ_PROD01 a schema or something?

I think you might have to specify that on the datasource= option. For example:

libname mydblib odbc user=testuser password=testpass datasrc=mydatasource;
0
votes

Have you tried the schema= option on the libname statement. The schema is equivalent to the first level name. 

libname edw odbc database=edw_nz user=&username. pw=&password. schema=edw;

proc sql;
  create table test as
    select *
    from EDW_XYZ_PROD01..Table_xyz;
quit;