topic Re: Proc SQL, odbc connection in SAS Programming

Proc SQL, odbc connection

yangx — Fri, 31 Aug 2018 18:06:22 GMT

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

Re: Proc SQL, odbc connection

DBailey — Fri, 31 Aug 2018 18:51:58 GMT

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;

Re: Proc SQL, odbc connection

yangx — Fri, 31 Aug 2018 19:09:32 GMT

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

Re: Proc SQL, odbc connection

DBailey — Fri, 31 Aug 2018 19:18:04 GMT

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

Re: Proc SQL, odbc connection

yangx — Fri, 31 Aug 2018 19:21:05 GMT

Thank you very much for your help!