Connect two databases and to do the selection

[Fawkes]

Hi guys,

Now I have 40 values for one variable (character). I would like to use these 40 values to generate a list and apply this list in another database to select certain observation. For example, I have 40 names of dog breeds in one database (A) and another database (B) contains information of dog id, breed name, and dog weight. What I need to do is to choose the dog ID from the second database (B) whose breed name appear in the database A.

My plan is to create a character list with the function concatenation and then I got stuck. How should I introduce this list into the database A? Does anybody do something similar and give me some suggestions? Any suggestion is appreciated.

Best regards,

Fawkes

[RW9]

Have a look at the documentation on fedsql: http://support.sas.com/documentation/cdl/en/fedsqlref/67364/HTML/default/viewer.htm#titlepage.htm

Although that is probably overkill.  Just connect to each database separately, and extract the data in SAS.  Then in SAS do your processing:

proc sql;

     connect to ...;

     create table a as select * from connection... (select...);

     diconnect from ...;

     connect to ...;

     create table b as ...;

     disconnect...;

quit;

data xyz;

     ...;   /* Do processing */

run;

[acfarrer]

Have you considered using a SAS macro variable to pass list of values? It can be a very efficient technique.Something like this:

proc sql noprint ;

select quote(variable_name) into :value_list separated by ' ' from libname.source_table ; /* quote() may not be needed */

quit ;

%put &value_list ;  /* For checking */

Then query the other database:

select * from other_table where variable_name in ("&value_list") ;