Proc SQL, odbc connection

[yangx]

Hello,

I would like to get all the sas dataset through a libname that is created by using odbc connection, but I cannot use proc sql to select the sas dataset, while the same statement if libname is work directory, it works. Please see the program below.

libname mydblib odbc uid=unixtest pw=xxxxxx dsn=UATREP;

data gdemog4; set mydblib.G_DEMOG_4;
run;
proc sql ;
select memname into: memlist separated by " " from dictionary.tables where libname='WORK';
quit;

/*the libname work is worked, macro variable &memlist has value of gdemog4*/

proc sql ;
select memname into: sqlmeml separated by " " from dictionary.tables where libname='MYDBLIB';
quit;

/*the libname MYDBLIB is not worked. the macro variable sqlmeml is not created */

Could you please help me with understanding why the libname MYDBLIB created by ODBC is not recognized in proc sql statement? thanks.

xiumei

[DBailey · Accepted solution]

I think the issue might be the dictionary tables.  Since it is not a sas base library, it might not know anything about the underlying data.

I suspect this will work:

proc sql;
create table work.gdemog4 as select * from mydblib.G_DEMOG_4;
quit;

If the underlying database is SqlServer, then this might work:

proc sql;
connect using mydblib as c;
select table_name into: sqlmeml separated by " " from connection to c (
  select table_name from information_schema.tables 
);
disconnect from c;
quit;

[yangx]

Dear DBailey,

Thank you so much for the solution. 

I tried your program, it worked!   The first time to get data from sqlserver, don't know where to find the information. If possible, could you please let me know where I can find the related information?

Kind regards,

Xiumei

[DBailey]

https://www.mssqltips.com/sqlservertutorial/179/sql-server-informationschema-views-tutorial/

[yangx]

Thank you very much for your help!