topic Re: join table simple in SAS Procedures

join table simple

sasphd — Sat, 23 Mar 2019 16:40:55 GMT

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

Re: join table simple

VDD — Sat, 23 Mar 2019 16:48:38 GMT


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;

Re: join table simple

VDD — Sat, 23 Mar 2019 16:53:39 GMT

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;

Re: join table simple

sasphd — Sat, 23 Mar 2019 16:55:59 GMT

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

Re: join table simple

VDD — Sat, 23 Mar 2019 17:17:22 GMT


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