BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasphd
Lapis Lazuli | Level 10

 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

1 ACCEPTED SOLUTION

Accepted Solutions
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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

 

View solution in original post

4 REPLIES 4
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Lapis Lazuli | Level 10

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
Ammonite | Level 13 VDD
Ammonite | Level 13

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

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 874 views
  • 0 likes
  • 2 in conversation