proc sql: making reference to data in another table

[Anita_n]

Hi all,

if I have a set of id's in table A(a subset of table B) and an tring to create a new dataset from table B. Is it possible to make a reference between the two tables (I want to make a selection depending on the pat_id's in table A)

for a examples using the code below: 

For example if table A conains the following Dataset

pat_id

1

2

3

and table B contains

pat_id  var1   var2

1           35       3.5

2           77        6.7

3          15         5.3

4           11         2.0

5                       1.2

6            54        8.9

proc sql;
create table dataset_new as select var1, var2 from tableB where pat_id in tableA;
quit;

[maguiremq · Accepted solution]

Use a sub-query:

data table_a;
input pat_id;
datalines;
1
2
3
;
run;

data table_b;
input pat_id var1 var2;
datalines;
1 35 3.5
2 77 6.7
3 15 5.3
4 11 2.0
5 . 1.2
6 54 8.9
;
run;

proc sql;
	create table 	want as
		select
					*
		from
					table_b
		where
					pat_id in (
									select 
												pat_id
									from
												table_a		)
		;
quit;

Obs pat_id var1 var2 
1 1 35 3.5 
2 2 77 6.7 
3 3 15 5.3 

More here: http://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/p1st65qbmqdks3n1mch4yfcctexi.htm