[SAS programming]Set operators과 all,corr modifiers

except, intersect, union, outer union의 기본 개념과 이를 더 풍요롭게 이용할 수 있는 all, corr modifier를 다뤄보자: )

Set은 집합이다. 집합의 가장 큰 특징은 다수 중복된 값이 있다면 그중 하나만 남게 된다는 것이고 ,

이를 '데이터’의 관점으로 적용해 말한다면, 중복되는(duplicate) data가 제거된다는 것이다.

추가적으로 여러가지 set operator(except, intersect, union,outer union)를 통해 data filtering할 수 있다.

Set operator로 다음과 같이 있는데, 하나하나 살펴보자.

• except
• intersect
• union
• outer union

그리고 기본적인 개념과 함께 같이 사용되는 all 과 corr의 옵션도 같이 확인해볼 예정이다.

‘그냥 중복만 제거하면 되는거 아니야?‘싶지만 더 섬세하게 데이터를 다루기 위해 알고있어야 하는 옵션이다. Modifier라고 불린다.

• all
• corr(corresponding)

위의 SET operator들의 기본적인 Default 성질을 정리해보면 다음과 같다.

set operator의 default behaviors

Modifiers

ALL -row 단위에서 조정

• (기본적인 set의 특징인,) 중복된 row를 제거하기 않고 avoids an extra pass through the data.
• OUTER UNION에서는 이미 all의 의미가 내제되어 있기 때문에 all modifier 사용이 불가능하다.

CORRESPONDING(CORR) -column 단위에서 조정

• 테이블 컬럼의 position이 아닌 name을 기준으로 set operator들이 작동하게 한다. 뭔 말인가 싶긴 하겠지만, 차후에 적용된 사례를 보면 이해가 될테니 걱정하지 말자.
• name을 기준으로 set operator들이 작동하기 때문에 두 테이블에서 공통인 컬럼들만 선택되고, 둘중 하나인 컬럼은 remove된다.
• Outer Union에서 사용이 된다면 어떻게 될까?

Set Operators

Except

‘except, 제외하다~’의 뜻으로 다들 알고있을 것이다. 결과는 차집합이라고 생각하면 된다. 첫번째 테이블(default table!)을 기준으로 두번째 테이블과 공통된 row는 제거되고(except의 기능), 그중 unique한 값만 남는다(set의 기능).

except 의 결과는 차집합!

table_one과 table_two를 만드는 코드는 다음과 같다.

data one;
input x a$;
cards;
1   a       
1   a       
1   b       
2   c       
3   v       
4   e       
6   g
;
run;
data two;
input x b$;
cards;
1 x
1 y
3 v
5 w
;
run;

table one

table two

Except의 가장 간단한 형태도 결과를 확인해 보자. 테이블 one을 보면 컬럼이 x,a 이고 테이블 two를 보면 컬럼이 x,b로 두번째 컬럼이름이 서로 다르다. 기본 except는 테이블 one을 기준으로 컬럼의 이름은 상관 없이 position의 데이터만 고려하여 데이터 filtering이 진행된다.

proc sql;
select *
 from one
except 
select *
 from two;
quit;

output of simple except query

output을 확인해보면 테이블 one과 two의 공통인 row 3, v가 table one에서 제거됨을 확인할 수 있다. 그리고 위에서 언급되었지만, 기준은 첫번째 테이블이기 때문에 output table의 이름 또한 첫번?째 테이블을 따른다.

Except all

except의 차집합의 filtering 기능은 동일하게 가지고 있지만 (set의 기본 성질인) 중복되는 데이터는 제거되지 않고 포함된다.

proc sql;
select *
 from one
except all
select *
 from two;
quit;

쿼리의 결과를 확인해보면 테이블 one과 two의 공통인 row 3, v가 table one에서 제거되었지만, 테이블 one에서 중복되는 1 a가 그대로 출력됨을 확인할 수 있다.

output of except all

Except corr

except의 차집합의 filtering 기능은 동일하게 가지고 있지만, 같은 이름의 컬럼의 한에서 데이터 필터링이 된다. (set의 기본 성질인) 중복되는 데이터는 제거된다.

proc sql;
select *
 from one
except corr
select *
 from two;
quit;

쿼리의 결과를 확인해보면 테이블 one과 two의 공통인 컬럼인 x만 출력이 되었고, 테이블 one을 기준으로 two에 없는 4,6만 선택되었다.

output of except corr

Except all corr

all과 corr modifier 가 둘다 있으면 어떨까? corr이 먼저 수행되어 다른 컬럼인 a와 b는 제거된다. 그 후, all이 수행되어 중복있는 데이터가 제거 되지 않고 그 상태에서 except이 실행되기 때문에 결과는 다음과 같이 된다

proc sql;
select *
 from one 
except all corr
select *
 from two;
quit;

output of except all corr

all corr 같이 쓰는건 헷깔린다고? 사실 필자도 헷깔린다. 사실 modifier를 둘다 쓰는 경우는 거희없다. 내가 모르면 버리면 된다. 추후에 where로 필터링을 추가하면 되니까 몰라도 문제될건 전혀 없다.

하지만 corr과 all이 있을때 실행 우선순위는 corr>all임은 기억해보자.

컬럼 필터링>데이터 필터링으로 해석하면 기억하는데는 문제 없을 것이다. 필터링을 할때 처음부터 섬세한 데이터 필터링을 하는 사람은 없으니까.

Intersect

intersect는 교차하다는 의미로 결과는 교집합과 동일하다. 첫번째와 두번째 테이블에서 공통의 데이터 중 unique 한 row만 선택된다.

intersect의 결과는 교집합!

proc sql;
select *
 from one
intersect
select *
 from two;
quit;

쿼리의 output을 확인한 결과 컬럼은 one 테이블의 기준으로 x와 a가 출력되었다. 데이터는 one과 two의 (부분집합) 공통의 데이터인 3,v이 선택되었음을 확인할 수 있다.

output of intersect

Intersect all

modifier ‘all’의 기능이 기억나는가? 맞다 set의 본연의 성질 파괴이다. 중복(duplicate) 데이터가 생략되지 않고 그대록 선택이 되어 나온다. 만일 intersect의 결과의 데이터가 중복이 되었다면, unique하게 한 row가 아닌 중복의 row가 그대로 출력이 되었을 것이다.

Intersect corr

modifier ‘corr’은 기억나는가? 바로 기본 set operator 작동전에 컬럼 필터링을 하는 것이라 위에서 말한바 있다. 그렇다면 테이블 one과 two에서 각각 컬럼 a,b는 제거되고 x에 대해서 intersect operator가 작동한다.

proc sql;
select *
 from one
intersect corr
select *
 from two;
quit;

output of intersect corr

Union

union은 조합, 결합이라는 뜻으로 쿼리의 결과는 합집합과 동일하다. 각각 테이블에서 unique한 모든 row들이 선택된다.

union의 결과는 전체집합!

proc sql;
select *
 from one
union
select *
 from two;
quit;

쿼리의 결과를 확인해보면 우선 테이블 one과 two에서 중복되지 않는 모든 row가 선택됨을 확인 할 수 있다.

output of union

Union all

all은 중복을 제거하지 않는다는 의미로 테이블 one과 two에 있는 모든 데이터가 선택이 된다. 실제의 활용도? 거희 없는 듯 하다. 쿼리와 결과는 다음과 같다.

proc sql;
select *
 from one
union all
select *
 from two;
quit;

output of union all

Union corr

계속 반복되는 내용이지만, 우선적으로 테이블에서 one과 two에서 공통인 컬럼만 선택된다. 즉 x만 남는다. 그후 union operator가 작동하고나서 중복되는 값이 제거된다. 쿼리와 결과는 다음과 같다.

proc sql;
select *
 from one
union corr
select *
 from two;
quit;

output of union corr

Outer Union

결과는 join에서의 full outer join과 유사하다. 두 테이블에서 unique한 데이터와 non-unique 한 데이터 모두 선택되어 합쳐진 형태이다. 하지만 각 테이블에 의해 만들어진 빈공간은 결측치로 남아있다. 쿼리의 output은 다음과 같다.

결과는 full outer join과 동일!

proc sql;
select *
 from one
outer union 
select *
 from two;
quit;

output of outer union

후,, 이상, 끝이다.

이제 자유롭게 set operator 기능을 사용하시길!!