topic Re: Connect two databases and to do the selection in Administration and Deployment

Connect two databases and to do the selection

Fawkes — Thu, 09 Jul 2015 11:11:28 GMT

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

Re: Connect two databases and to do the selection

RW9 — Thu, 09 Jul 2015 11:56:52 GMT

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;

Re: Connect two databases and to do the selection

acfarrer — Thu, 09 Jul 2015 13:36:54 GMT

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") ;