Chapter 3. Combining Tables Horizontally Using PROC SQL - (2) - USING INNER JOINS

INNER JOIN SYNTAX

Inner join과 Outer join 두 방법이 있다고 하고요. 이번 포스팅은 INNER JOIN에 대해 설명하고자 합니다. 

INNER JOIN은 위 그림처럼, 서로 교집합부분을 말하는데요. 

합치려고 하는 데이터테이블 중에서, 첫번째 테이블의 행과 매치되는 값을 두번째 데이터테이블에서 찾아서 그것만 아웃풋으로 보여달라는 내용입니다. 이때 어떤 값이 매치될지는 WHERE구문에서 조건절을 넣어줘야겠죠. 이때 syntax는 다음과 같습니다.

바로 예문으로 보도록 하죠. 

FROM구문에서 불러오고자 하는 데이터를 지정하고요. ONE, TWO라는 데이터테이블을 불러와서, WHERE구 보면, WHERE one.x = two.x 즉 one이라는 데이터의 x라는 열 이름과 two라는 데이터의 x라는 이름이 같은 경우를 조건으로 걸었네요. 조건이 없는 경우 Cartesian product (그 전 포스팅 참고하세요)로 3^2 = 9개의 행이 출력되어야하지만, where조건을 걸었기때문에 이 조건에 해당하는 것만 결과값으로 나오게 됩니다. 이때 X라는 열의 값이 2라는것만 one과 two에 있기때문에 이 값만 출력하게 됩니다. 

다행이도 one / two 데이터테이블의 X라는 열의 이름이 같지만 달라도 상관 없습니다. 예를들어 테이블 1에서는 id라는 열의 이름으로 가진것과 테이블 2에서는 empid라는 열의 이름으로 가진것들의 값이 같을 경우라는 조건을 걸려면~ 

WHERE TABLE1.id = TABLE2.empid 라고 적으면 됩니다. 

이때 결과값은 한줄로만 나와있지만 사실 프로세스는 다음과 같아요. Cartesian product방법으로 모든 조합의 행들이 만들어지고요. 그 다음 한줄 (row)마다 where 구의 조건이 만족하는지 확인하게 됩니다. 이때 where 구문의 조건이 만족되지 않으면 제거되어서 제거되지 않은 최종값이 결과값이 되어 출력하게 됩니다. 

* Eliminate & Duplicate Columns 

결과값을 다시 봅시다. 열의 이름이 X, A, X, B이렇게 되어있네요. 왜냐하면 SELECT * 라고 지정했기때문에 모든 열의 값을 보여달라는 의미였고 그래서 X값이 중복되어 나타나게 되는겁니다.  

중복된 열의 이름을 제거하고 싶다면!! 

SELECT구문에서 다음처럼 지정해주면 됩니다. one.X 즉 one이라는 데이터테이블의 X라는 열의 이름과, a, b, 라는 열읭 ㅣ름이라고 따로 이름을 지정해주면 되는거죠. 혹은 SELECT one.*, b 이렇게 지정해줘도 됩니다. 결과값은 둘 다 같아요. 

열의 이름을 바꾸고 싶다면~!!  

만약 열의 이름을 바꾸고 싶다!!할땐 as 새로운이름, 이렇게 지정해주면 되는데요. 

아래 코드보면 one.x as ID, two.x, a, b, 이렇게 콤마를 기준으로 4개가 지정이 되었기때문에 결과값은 4개의 열이 나오게 됩니다. 

같은 값을 가진 행만 출력하고 싶다면!!   

아래 그림처럼 THREE라는 데이터테이블과 FOUR라는 데이터테이블이 있는데 X라는 열의 이름의 값 중에서 같은 값만 가진것만 출력하고자 합니다. 그럼 다음처럼 코드를 적을 수 있는데요. SELECT *라고 했으니 두개 데이터테이블의 열의 이름이 나 나오겠죠. 그래서 결과값은 아래 그림과 같습니다. 

예) Complex PROC SQL Inner Join  

SASUSER 라이브러이에 Staffmaster라는 데이터테이블엔, EmpID, LastName, FirstName, State가

SASUSER 라이브러리에 Payrollmaster라는 데이터테이블엔, EmpID, JobCode, DateOfBirth라는 열이 있다고 합시다. 

뉴욕에 살고있는 직원의 이름(First Initial, Last Name),  Jobcodes, age 데이터를 만들고자 할때 (이때 15개의 행만 보도록 합시다) 어떻게 하면 되는지 살펴보아요. 이름, Jobcode, 나이 이렇게 세개의 열을 만들고자 하는거거요. Jobcode만 Payrollmaster에 있네요? 

출력된 아웃풋그림부터 살펴보면 다음과 같아요. Name이른 열 이름에선 이름의 첫번째 글자에 . (period) 점을 찍어놓고 성을 적어놓았고요. Age는 태어난 연도와 올해 계산된 값이 들어간거겠어요. 

SELECT구에서 as Name, job code, as Age 이렇게 적은걸 보니 세개의 열을 만들고자 하고요. 

substr이라는 기능을 통해 firstname이라는 열의 1, 즉 첫번째 글자에서 1개만 불러오고 거기에 .(period)점을 찍은 뒤에 따옴표가 바로오지 않고 공백 있고 따옴표가 있는거 보니 점 찍고 한 칸 띄고요. 그리고 성을 붙이네요(||). 

Int함수는 integer그러니까 소숫점 없는 숫자가 나오는데, 오늘날짜(today())에서 태어난날을 빼서 그걸 365일로 나눠주면 해(year)가 나올텐데 소숫점으로 나올테니 소숫점을 없애주려고 int함수를 쓴거고요. 

where구문보면 payrollmaster의 empid와 staffmaster데이터의 empid가 같아야하고 그리고(AND) state가 NY일때만 걸러줘야겠지요. 그래서 코드는 다음과 같습니다. 

다음 포스팅엔 cutter join에 대해 살펴보도록 하죠. 

# SAS 자격증, SAS advanced, SAS advanced programming, SAS PREP GUIDE, 자격증 

Chapter 3. Combining Tables Horizontally Using PROC SQL - (1)

아래 그림처럼 여러개의 데이터테이블을 가져와서 양 옆으로 합쳐 새로운 데이터테이블을 만드는게 JOIN이라고 합니다. 이렇게 합칠때 어떤 값을 매치해서 합쳐야할지 조건이 들어가야겠죠. 이렇게 합칠때 꼭 Table A와 Table B가 같은 갯수의 행이 있어야하는건 아닙니다. 서로 행의 갯수가 다를수도 있어요. 

이렇게 데이터테이블을 JOIN할때 어떻게 합칠것인지 정해야하는데요. 유형이 크게 두가지가 있습니다. 

첫번째는 INNER JOIN이라고, 테이블끼리 서로 매치되는 (같은 값을 가지는) 것만 행을 합치는 방법. 

두번째는 OUTER JOIN이라고 첫번째와 거기에 서로 매치되지 않는 것까지 다 합칠때를 말합니다. 

둘 중 어느 방법을 사용하더라도 Cartesian Product(곱집합)가 계산되어 테이블끼리 어떻게 합칠 수 있는지 경우의 수가 계산되는데요. Cartesian product가 뭔지 살펴보도록 하죠. 

Cartesian product

아래 그림처럼 One, Two 두 테이블이 있다고 합시다. 이때 proc sql에서 select * 했을때 나오는 결과값이 그 아래 그림과 같은데요. 아래 코드처럼 FROM구문에서 불러올 데이터를 선택하지만 WHERE구문의 조건을 걸어두지 않을때 아웃풋은 Cartesian product방법을 통해 결과 데이터테이블이 만들어집니다. 

데이터 테이블에서 행의 갯수가 적을때 괜찮지만 만약 One, Two데이타의 행의 갯수가 1000개라고 하면 1000^2 = 1,000,000행이 만들어지겠죠. 이럴땐 SAS Log 파일창에 이런 메시지가 뜨게 됩니다.  

NOTE: The execution of this query involves performing one or
more Cartesian product joins that cannot be optimized.

그럼 다음 포스팅에서 Inner Joins에 대해 자세히 다뤄보도록 하겠습니다. 

# SAS 자격증, SAS advanced, SAS advanced programming, SAS PREP GUIDE, 자격증 

Chapter 2. Performing Advanced Queries Using PROC SQL (8) - QUIZ

1. 1. Which PROC SQL query removes duplicate values of MemberType from the query output, so that only the unique values are listed?

   
   

   a. proc sql nodup; 

           select membertype

               from sasuser.frequentflyers;

   
   

   b. proc sql;
           select distinct(membertype)

                       as MemberType
               from sasuser.frequentflyers;

   
   

   c. proc sql;
           select unique membertype

                   from sasuser.frequentflyers 

                   group by membertype;

   
   

   d. proc sql;
           select distinct membertype

                   from sasuser.frequentflyers; 

   
   

   
   

   unique 값이라고 하면 중복된 값이 없어야겠죠? 그래서 결과값에서 중복된 값을 제거하고 싶을때 특별히 쓰는 키워드가 있습니다. DISTICT였죠. 이 단어는 열(column) 전에 들어가야 합니다!! 즉, DISTINCT 변수(열)이름!!  따라서 답은 d 입니다. 

   
   

   
   

   2. Which of the following causes PROC SQL to list rows that have no data in the Address column?

   a. WHERE address is missing

   b. WHERE address not exists

   c. WHERE address is null

   d.   both a and c 

Address라는 열에 데이타가 없는 경우를 묻고 있네요. 데이타 값이 들어가있지 않은 행을 리스트할 경우, 조건 연산자인 IS MISSING 혹은 IS NULL을 이용할 수 있습니다. NOT EXISTS 연산자는 subquery에서 사용할 수 있습니다. 그래서 답은 d 입니다! 

3. You are creating a PROC SQL query that lists all employees who have spent (or overspent) their allotted 120 hours of vacation for the current year. The hours that each employee used are stored in the existing column Spent. Your query defines a new column, Balance, to calculate each employee's balance of vacation hours.

Which query produces the report that you want?

a. proc sql;

        select name, spent,

                    120-spent as calculated Balance 

                from Company.Absences

                where balance <= 0;

질문부터 봅시다. PROC SQL을 이용해서 리스트를 작성하려고 하는데요. 모든 직원의 사용한 휴가 시간과 120시간의 할당된 시간에서 쓸 수 있는 시간을 새롭게 계산해서 새로운 열 이름인 Balance로 저장하려고 합니다. 그러면 들어가야할 열 이름이 name, spent이고 balance는 120 시간에서 spent 을 뺀 값이 되겠지요. 제일 중요한건 balace라는 새로운 열은 기존에 있는 spent와 120시간의 차이의 계산된 값이 들어간다는건데요. 이럴때 CALCULATED라는 키워드가 필요하고 이 키워드는 WHERE구문에 들어가야합니다! 따라서 답은 B이예요. 

4. Consider this PROC SQL query:

proc sql;
        select flightnumber,

count(*) as Flights, 

avg(boarded) 

label="Average Boarded" 

format=3.

from sasuser.internationalflights 

group by flightnumber
having avg(boarded) > 150;

The table Sasuser.Internationalflights contains 201 rows, 7 unique values of FlightNumber, 115 unique values of Boarded, and 4 different flight numbers that have an average value of Boarded that is greater than 150. How many rows of output is generated by the query?

a. 150 

b. 7
c. 4
d. 1 

Sasuser라이브러리에 있는 internationalflights라는 데이터에는 201의 열과 7개의 FlightNumber가 있고, 115개의 유니크한 값을 가진 Boarded라는 열이 있고요. 이때 Boarded의 평균 값인 150보다 큰 값을 가진 flight numbers로는 4개가 있다고 합니다. 위에 쿼리에서 아웃풋의 행이 몇개인지 묻는데요~ 답은 C. 4개 입니다. group by 로 flightnumber로 그룹이 되어있네요. flightnumber는 총 7개가 있다고 했었죠. Having은 그룹화된 값에서 필터역할을 하는것으로 150보다 이상의 값을 가진 것만 보여달라는 뜻입니다. 150보다 큰 값을 가진건 4개라고 했으니 답은  C! 

# SAS advanced, SAS 자격증, SAS PREP GUIDE 

Chapter 2. Performing Advanced Queries Using PROC SQL (7) - Subqueries 

Introducing Subqueries 

PROC SQL에서는 WHERE이나 HAVING구절 말고도 이와 비슷한 기능을 하는게 있는데요. 바로 Subquery입니다. Subquery는 쿼리 안에 있는 또 다른 쿼리라고 보면 되어요. 그래서 Subqueries는 nested queries, inner queries 혹은 sub-selects라고 부르기도 합니다. 아래 그림처럼 쿼리 안에 subquery가 있는데요. 이렇게 안에 있는 쿼리는 괄호 ( ) 안에 넣어줘야 합니다. 

이렇게 inner query는 불러오는 테이블에서 하나 혹은 그 이상의 행을 선택해서 한개 혹은 여러개의 cutter query에서 실행하게 됩니다. 지금 inner query에서는 sasuser라이브러리에 payrollmaster라는 데이터에서 salary값의 평균값이 불러오게 되네요. 그렇다고 항상 inner query가 outer query와 연관 있느냐! 이건 아니예요. 

Noncorrelated / Correlated 이렇게 두가지로 유형이 나눠집니다. 그럼 이 두가지 유형에 대해 살펴보도록 하죠. 

Subsetting Data By Suing Noncorrelated Subqueries 

Noncorrelated subqueries란 inner query와 outerquery가 서로 연관이 없는 경우를 말합니다. 위에 코드가 그 예인데요. 다시 한번 살펴보죠. 

코드를 보면, HAVING 구문에서~ 아! HAVING 구문이 뭔지 모르겠다면 그 전 포스팅 참고하세요. 

Having 구문 안에 subquery가 있네요. 이 subquery는 하나의 값, 즉 sasuser 라이브러리에 있는 payrollmaster의 salary라는 데이터의 평균값, 하나의 값을 말합니다. 이 하나의 값을 가지고 그룹화되어있는 avg(salary) 값과 비교하라는 뜻이겠네요. 

그래서 jobcoder그룹별의 평균값과 전체 평균값을 비교하기위해 이렇게 쓰고 있습니다. 

이렇게 inner와 outer 쿼리가 서로 연관되지 않은 경우, sas 는 inner먼저 실행하고 그 다음 outer쿼리가 실행이 됩니다. 

그래서 위에 코드 보면, inner쿼리 안에서의 전체 샐러리의 평균값이 계산되어지고요, 그 다음 jobcode의 그룹화 평균값이 계산되어지는데 전체 평균값보다 큰 그룹의 평균값만 선택(select)되어 결과값으로 출력하게 됩니다. 그래서 결과값은 아래와 같아요. 

Using Multiple-Value Noncorrelated Subqueries  

위의 예는 inner 쿼리가 하나의 값만 가지게 된 경우였는데요. 만약 inner 쿼리가 여러개의 값을 가지게되는 경우는 어떨까요. 

예를들어, 생일이 곧 다가오는 회사 직원에게 생일카드를 보내고 싶어요. 그래서 PROC SQL을 이용해서 2월달 생일을 맞이하는 직원의 이름과 주소를 데이터로 가져오려고 합니다. 그런데 이름과 주소가 한 데이터 테이블, 즉 Staffmaster란 파일에 있고요. 생일은 다른 데이터테이블, 즉 payrollmaster라는 파일에 있다고 칩시다. 이 두개의 파일에는 EmpID 열이 공통으로 가지고 있습니다. 

위에 코드 보면, inner 쿼리부터 실행된다고 했으니~ inner 쿼리를 봅시다. 괄호 안에, sasuser라이브러리에 있는 payrollmaster라는 파일에서 dateofbirth의 달이 2인 empid를 가진 사람만 선택되어 나옵니다. 위의 예와 다르게 여러명의 empid값들이 선택되어지겠죠. 그래서 outer 쿼리의 where구문 보면, where empid in 즉, where 구문의 조건 연산자인 IN이 나와서 이 조건을 만족하는 empid와, 성, 이름, 도시, 주 가 선택되어 결과값이 나오게 됩니다. 그래서 결과값은 다음과 같아요. 

Subsetting Data By Suing Correlated Subqueries 

이번엔 쿼리들끼리 연관되어있는 경우를 살펴보도록 합시다. 바로 예제로 들어갈게요. 

navigator이면서 매니저인 사람들의 성과 이름만 출력하려고 하는데, 데이터가 행으로 읽어지는데 jobcategry가 'NA'인 사람이 sasuser라이브러리에 있는 supervisors라는 데이타에서 staffmaster라는 데이터의 EmpID가 suvervisors라는 데이터의 EmpID가 같을 경우 그 jobcategory가 선택되어져서 이것들이 NA와 같은 경우, 이럴때만 그 사람의 성과 이름을 출력하라는 내용입니다. 그러니까 행마다 데이터를 읽어내서, 그 행의 jobcategory가 NA일때, 이 NA가 inner 쿼리 값과 비교하는거라서 서로 연관되어있다고 보는겁니다. 

결과값은 아래와 같고요. 이 사람들이 navigator이면서 (즉 jobcategory가 NA이면서) 매니저인 사람들입니다.

Using the EXISTS and NOT EXISTS Conditional Operator 

이때 outer쿼리에 있는 WHERE 구문이나 HAVING 구문의 조건이 어떠냐에 따라 EXISTS 혹은 NOT EXISTS 두가지로 나눠지는데요. 말 그대로 inner 쿠리의 값이 하나 이상 해당될때가 EXISTS, 하나도 해당되는 값이 없을때  NOT EXISTS로 나눠집니다. 

NOT EXISTS의 예를 들어봅시다. 

Flightattendatns라는 파일에는 모든 승무원의 이름과 empID가 들어있고요. 

FlightSchedule이라는 파일에는 각 날짜별로 일하는 승무원의 정보가 있다고 합시다. 

만약 두개의 파일의 공통적으로 나타나는 승무원이 있다면~ 

승무원이면서도 스케쥴이 잡힌 승무원을 말하겠죠? 

아래 그림과 같은 경우, 승무원이지만 아직 스케쥴이 잡히지 않은 승무원을 말합니다. 

지금 아래 그림에 해당하는 승무원이 있는지 찾아보고자 해요. 

다음과 같은 코드를 사용하면 됩니다. 

sasuser라이브러리에 있는 flightattendatns라는 데이터에서 성과 이름을 출력하는데 누구를 선택하느냐!! 조건이 필요하겠죠. 그 조건 where은 NOT EXISTS 즉, inner 쿼리 안에서 살펴볼때, flightschedule이라느 데이터 파일에서 flightattendant의 empID인 사람이 flightshcedule의 empID와 같은 모든 경우를 찾아보고 얘네들을 선택해서 (select *), 여기에 해당되지 않은 경우 (not exists)의 성과 이름을 출력하란 내용입니다. 

# sas 자격증, SAS Advanced, SAS advanced Programming, SAS Prep Guide, 자격증 

Chapter 2. Performing Advanced Queries Using PROC SQL (6) - Grouping & Having   

Selecting Groups By Using the HAVING Clause 

그룹화하는거는 지난 포스팅에 다뤘고요. (http://statnmath.tistory.com/276)

왼쪽처럼 결과값이 있으면 AvgSalary가 56000 이상의 값만 보여주는건 어떻게 하는지 알아보겠습니다. 그래서 원하는 결과값이 오른쪽 그림처럼 되는거죠. 

그룹화(Grouping)된 상태에서 조건을 설정할 경우 HAVING 구절을 사용합니다. 

아래 코드 보면, sasuser라이브러리에 payrollmaster데이터를 가지고 jobcode와 salary 값은 평균값으로 AvgSalary라는 변수이름으로 저장하고요. 이때 포맷은 달러사인으로 소숫점자리 두자리까지 표시합니다. 이때 jobcode는 그룹화하고 salary의 평균값이 56000만 보여지도록 하라는 내용입니다. 그래서 HAVING은 마치 필터역할을 한다고 보시면 돼요. 그래서 HAVING구절을 만족하는 그룹만 보여지게 됩니다. 

그럼 WHERE구와 다른 점은 뭐냐~!! 하면~ 

WHERE구는 각 개별의 행(row)에 해당하고요. HAVING 은 그룹별로 조건을 살펴보게 됩니다. 

만약 HAVING구절은 있는데 GROUP BY가 생략된 경우, 데이터 전체를 하나의 그룹으로 판단해서 결과값이 나오게 됩니다.그래서 평균값이 56000이상이면 전체 행이 테이블로 나오게 되고요. 평균값이 56000이하면 해당하는 값이 아니라서 아무런 행(row)이 나타나지 않게 됩니다.  

# SAS 자격증, SAS Advanced, SAS Advanced programming, SAS prep guide, 자격증