join table simple

[sasphd]

 I want to join two table in this ways

table A

id mret

1   8

2   5

3   9

table B

id MP

1   96

2   6

3   5

Table want 

id mret MP

1   8     96

2   5     6

3   9      5

[VDD]

data A;
input id mret;
datalines;
1   8
2   5
3   9
;
proc sort data=a;
	by id;
run;
data B;
input id MP;
datalines;
1   96
2   6
3   5
;
proc sort data=bl
	by id;
run;
data want;
	merge a b;
	by id;
run;

[VDD]

or with SQL

data A;
input id mret;
datalines;
1   8
2   5
3   9
;

data B;
input id MP;
datalines;
1   96
2   6
3   5
;
proc sql;
	create table want as
	select a.*,b.mp
	from a as a,
	     b as b
	where a.id=b.id;
quit;

[sasphd]

can you please give me a solution which did not use id because I have same id missing.

just add a column to table A form table b

[VDD · Accepted solution]

data A;
input id mret;
datalines;
1   8
2   5
3   9
.   3
;
proc sort data=a;
	by id;
run;
data B;
input id MP;
datalines;
1   96
2   6
3   5
.   33
;
proc sort data=b;
	by id;
run;
data want;
	merge a b;
	by id;
	where id ne .;
run;

proc sql;
	create table want as
	select a.*,b.mp
	from a as a,
	     b as b
	where a.id=b.id
	     and(a.id ne . and b.id ne .);
quit;

the solutions provided were quick here is how you can do that.

for a more in-depth solution use the same concept as this accepted solution. 

https://communities.sas.com/t5/SAS-Procedures/repeat-observation-by-id/m-p/490130#M72083