Join
Cross Join
Data Demo;
Input id name height
1 x 1
3 y 2
5 z 2
7 w 2
9 m 2
run;
Data Demo1;
Input id name weight
2 x 2
4 y 3
5 z 4
7 w 5
run;
proc sql dummy as
select * from demo as A cross join Demo1 as B;
Quit;
Inner Join:
Proc sql;
Create table dummy as
Select * from Demo as A, Demo1 as B
Where Demo.id=Demo1.id;
Quit;
Or
Proc sql;
Create table Dummy as
Select * from Demo as A Inner Join Demo1 as B
On A.id=B.id;
Quit;
Data step code for Inner Join:
Data Dummy
Merge Demo (In =A) Demo1 (In=B) by id;
If A and B
Run;
Left Join:
Proc sql;
Create table dummy as
Select * from Demo as A Left Join Demo1 as B
On A.id=B.id;
Quit;
Data Step for Left Join:
Data Dummy;
Merge Demo(in =A) Demo1(on=B)
BY id;
If x;
Run;
Right Join:
Proc sql;
Create table dummy as
Select * from Demo as A right join Demo1 as B
On A.id=B.id;
Quit;
Data Set Code:
Data Dummy;
Merge Demo(in=A) Demo1(in=B);
BY id;
If B;
Run;
Full Join: The full join suffer the same difficulty as the right join. Namely, the common variable values are lost from the right hand data set. The coalesce function can solve this issue.
Proc sql;
Create table dummy as
Select coalesce (A.id,B.id) as id ,coalesce(A.name,y.name)As name ,hight,weight
from demo as A full join Demo1 as y
on A.id=B.id;
quit;
Data Set Code:
Data dummy
Merge demo,demo1;
By id;
Run;
