5.2.3. 데이터 병합

1. MERGE

mysas.subject_test1_1

data mysas.subject_test1_1;
input id name $9. class_ $ subject $ score ;
datalines;
101 이호준 1반 computer 92 
102 이호중 1반 computer 80
201 이길동 2반 computer 90
202 이준호 2반 computer 86
;
run;
proc print data=mysas.subject_test1_1;
run;

 

mysas.subject_test2

data mysas.subject_test2;
input id phonenumber $16.;
datalines;
101 010-1234-5678
102 010-9876-5432
201 010-4567-1565
202 010-7536-9512
;
run;
proc print data=mysas.subject_test2;
run;

 

data mysas.join;
merge mysas.subject_test1_1 mysas.subject_test2;
by id;
run;

proc print data=mysas.join;
run;

proc sort data=라이브러리명.데이터셋;
by 정렬할 변수;
run;

proc sort data=mysas.subject_test1_1;
by id;
run;

proc sort data=mysas.subject_test2;
by id;
run;

2. 조인의 종류

2.1 full join

data mysas.phonehw;
input id name $10. type $ number$16.;
datalines;
101 이호준 company 004-050-1200
101 이호준 phone 010-1234-5678
102 이호중 home 012-134-7894
201 이길동 phone 010-4567-1565
202 이준호 phone 010-7536-9512
;
run;

proc print data=mysas.phonehw;
run;

data mysas.subject_test1_1;
input id name $9. class_ $ subject $ score ;
datalines;
101 이호준 1반 computer 92 
102 이호중 1반 computer 80
201 이길동 2반 computer 90
202 이준호 2반 computer 86
;
run;

proc print data=mysas.subject_test1;
run;

proc sort data=mysas.phonehw;
by id;
run;

proc sort data=mysas.subject_test1_1;
by id;
run;

data mysas.fulljoin;
merge mysas.subject_test1_1 mysas.phonehw;
by id;
run;

proc print data=mysas.fulljoin;
run;

2.2 inner join

 

data mysas.phonehw2;
input id name $10. type $ number$16.;
datalines;
101 이호준 company 004-050-1200
101 이호준 phone 010-1234-5678
102 이호중 home 012-134-7894
201 이길동 phone 010-4567-1565
202 이준호 phone 010-7536-9512
203 이준길 phone 010-1563-4595
;
run;
proc print data=mysas.phonehw2;
run;

proc sort data=mysas.phonehw2;
by id;
run;

proc sort data=mysas.subject_test1_1;
by id;
run;

data mysas.innerjoin;
merge mysas.phonehw2(in=emps) mysas.subject_test1_1(in=cell);
by id;
if emps=1 and cell=1;
run;
proc print data=mysas.innerjoin;
run;

2.3 right join

data mysas.rightjoin;
merge mysas.subject_test1_1(in=emps) mysas.phonehw2(in=cell);
by id;
if emps=0 and cell=1;
run;

 

2.4 left join

data mysas.data11;
merge mysas.data3(in=emps) mysas.data9(in=cell);
by name;
if emps=1 or cell=0;
run;

2.5 둘다 0인 경우

data mysas.data11;
merge mysas.data3(in=emps) mysas.data9(in=cell);
by name;
if emps=0 or cell=0;
run;

3. SET

data 라이브러리.통합데이터셋;
set 라이브러리.데이터셋1 라이브러리.데이터셋2;
run;

data mysas.set1;
set mysas.subject_test1_1 mysas.phonehw2;
run;
proc print data=mysas.set1;
run;

3.1 칼럼명이 일치하지 않는 경우

3.2 칼럼의 속성이 다른 경우