BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yangx
Obsidian | Level 7

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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;

View solution in original post

4 REPLIES 4
DBailey
Lapis Lazuli | Level 10

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
Obsidian | Level 7

 

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

 

yangx
Obsidian | Level 7

Thank you very much for your help!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1066 views
  • 1 like
  • 2 in conversation