joining 3 tables into one

[monday89]

I have three data sets

1. Patient Level - all unique patient with patient ID [table1]

patientID

123-ABC

123-DEF

123-GHI

123-JKL

123-MNO

123-PQR

123-STU

2. Reference table - Reference name for each number [table2]

category	number
ABC	1
DEF	2
PER	3
WER	4
QWE	5

3. Patient level having certain patient number [table3]

patientID	number
123-ABC	1
123-DEF	1
123-GHI	2
123-GHI	3
123-JKL	4
123-JKL	2
123-MNO	5

What I want is that combine all THREE tables and have 1 or 0 even though there is no outer join. The patient list should have 1 or 0 for each reference number when appropriate 

patientID	category	measure
123-ABC	ABC	1
123-ABC	DEF	0
123-ABC	PER	0
123-ABC	WER	0
123-ABC	QWE	0
123-DEF	ABC	1
123-DEF	DEF	0
123-DEF	PER	0
123-DEF	WER	0
123-DEF	QWE	0
123-GHI	ABC	0
123-GHI	DEF	1
123-GHI	PER	1
123-GHI	WER	0
123-GHI	QWE	0
123-JKL	ABC	0
123-JKL	DEF	0
123-JKL	PER	1
123-JKL	WER	1
123-JKL	QWE	0
123-MNO	ABC	0
123-MNO	DEF	0
123-MNO	PER	0
123-MNO	WER	0
123-MNO	QWE	1
123-PQR	ABC	0
123-PQR	DEF	0
123-PQR	PER	0
123-PQR	WER	0
123-PQR	QWE	0
123-STU	ABC	0
123-STU	DEF	0
123-STU	PER	0
123-STU	WER	0
123-STU	QWE	0

I tried the following

proc sql;

select a.patientid, b.category, b.number

from table1 a 

left join table3 b full outer join a.patientid = b.patientid;

quit;

i am not sure how to use inner join

[unison · Accepted solution]

This is what you need:

proc sql;
	create table want as
	select 
		t1_t2.patientid
		, t1_t2.number
		, (t1_t2.number=t3.number) as measure
	from 
		(select t1.*, t2.* from tbl1 t1, tbl2 t2) t1_t2 
	left join tbl3 t3 on 
		t1_t2.patientid=t3.patientid and
		t1_t2.number=t3.number
	order by t1_t2.patientid, t1_t2.category;
quit;

Cartesian join between t1 and t2 (call it t1_t2), then you want to left join t3 on patientid and number then create measure as a flag indicating if t1_t2.number = t3.number.

Full code below:

data tbl1;
	input patientid :$7.;
	datalines;
123-ABC
123-DEF
123-GHI
123-JKL
123-MNO
123-PQR
123-STU
;
run;

data tbl2;
	input category $ number;
	datalines;
ABC 1
DEF 2
PER 3
WER 4
QWE 5
;
run;

data tbl3;
	input patientid :$7. number;
	datalines;
123-ABC 1
123-DEF 1
123-GHI 2
123-GHI 3
123-JKL 4
123-JKL 2
123-MNO 5
;
run;

proc sql;
	create table want as
	select 
		t1_t2.patientid
		, t1_t2.number
		, (t1_t2.number=t3.number) as measure
	from 
		(select t1.*, t2.* from tbl1 t1, tbl2 t2) t1_t2 
	left join tbl3 t3 on 
		t1_t2.patientid=t3.patientid and
		t1_t2.number=t3.number
	order by t1_t2.patientid, t1_t2.category;
quit;