BookmarkSubscribeRSS Feed
Fawkes
Calcite | Level 5

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1245 views
  • 8 likes
  • 3 in conversation