Chapter 2. Performing Advanced Queries Using PROC SQL (5)

Using a Summary Function  

아래 아웃풋 두개가 있는데요. 서로 비교해면 왼쪽 그림은 JobCode가 있고 거기에 평균 salary가 있습니다. 그런데 평균 샐러리가 모두 다 같은 값이네요. 사실은 오른쪽 그림의 아웃풋처럼 JobCode별의 평균 샐러리를 구하는 아웃풋을 원하는거였는데 말이지요. 

왼쪽 아웃풋의 명령어는 다음과 같습니다. 20개의 값(행)만 출력하고요. sasuser 라이브러리에 있는 payrollmaster라는 데이터를 불러워서 jobcode의 열을 불러오고 salary의 평균(avg)값을 구해서 AvgSalary라는 열을 새로 만들어 넣으라는 내용입니다. 따라서 결과값은 jobcode가 20개의 행만큼 주르륵 나오고 AvgSalary는 salary의 그냥 평균값, 값 하나인 54079.62만 들어가게 됩니다. 이때 outobs가 설정되지 않으면 payrollmaster의 148개 행이 모두 출력됩니다. 하나의 값이 계속 반복되어 출력이 될 경우 SAS log파일에 이에 관한 메시지가 뜨게 됩니다. 이건 나중에 다루도록 하고요. 

 JobCode별 평균 샐러리를 구하고자 한다면 Group by 구문이 필요합니다. 

아래 코드보면 첫번째로 outobs = 이 구문이 없어졌네요. 그룹화하게되면 행의 수가 적어지게 되어서 제외되었고요. AvgSalary의 포맷이 다르게 설정된거 이외에 추가된 내용은 group by jobcode; 입니다. 

Counting Values by Using the COUNT Summary Function

전체 행의 개수나 아니면 특정 그룹의 행을 세기 위해서는 다음과 같은 함수를 사용하면 됩니다. 

전체 행의 개수를 세고자 할때~ 

count (*) as Count 즉 count라는 열을 생성해서 불러오는 데이터의 모든 행을 세어라는 뜻입니다. 아래 예시처럼 payrollmaster의 모든 행의 갯수가 Countf라는 변수이름 아래 생성이 됩니다.  

만약 그룹별 행의 갯수를 세고 싶다면!!! 

아래 코드를 보죠. SELECT구문을 이용해서 Job Catogory라는 새로운 열을 만드는데 이때 sasuer라이브러리에 있는 payrollmaster의 데이타에서 jobcode의 열 중에 1번부터 2번까지의 문자열을 불러와 Job Category에 넣습니다. 그리고 두번째 열은 Count로 Job Category로 그룹화된것으로 행을 센 값이 들어가게 됩니다. 하지만 주의해야할점은 만약 missing value가 있으면 결과값이 다르게 나올 수 있어서 missing value가 있는지 확인하는 절차가 필요할 수 있습니다. 

Non-Missing Values 세어보기 

missing value를 바로 세어볼 수 있지만, 만약 행의 갯수를 알고 실제 관측값을 세어보았을때 둘의 값이 일치하면 missing value가 없다는 뜻이겠지요. 혹은 실제로 값이 들어있는 행의 갯수를 세어보고싶을때 아래처럼 코드를 사용하면 됩니다. count (변수이름) as Count 라고 하면 Count의 변수이름 아래 JobCode 열의 관측치의 갯수가 들어가게 됩니다. 

All Unique Values 세어보기 

JobCode가 한 열에서 반복적으로 나타날 수 있잖아요. 몇개의 JobCode가 있는지 Unique Value를 셀 때 distinct의 키워드를 넣으면 됩니다. 아래 코드 보면 Count라는 열을 만들어서 jobcode의 unique value가 몇개인지 distinct라는 키워드를 사용해서 코드를 넣었네요. 그래서 결과값 보면 16개가 나와있죠. 즉, 16개의 jobcode가 있다는 뜻입니다. 

#  SAS 자격증, SAS Advanced, SAS Advanced programming, SAS Prep Guide, 자격증 

Chapter 2. Performing Advanced Queries Using PROC SQL (4)

Enhancing Query Output 

쿼리 아웃풋에 관한 내용입니다. 먼저 아래 코드를 살펴보죠. 결과값으로 15개만 출력하고자 하고요. sasuser라이브러리에 있는 payrollmaster란 데이터를 불러오는데, SELECT 구문을 통해 그 데이터에 있는 empid, jobcode, salary 세 개의 열을 불러오고요. 다시 salary에 0.10을 곱한 값을 Bonus라는 새로운 변수의 열을 만들어줍니다. Where구문으로 salary 값이 75000 이상만 보여지게 하고요. 그리고 order by 구문을 이용해서 salry값은 내림차순(DESC)으로 합니다. 

결과값은 다음과 같습니다. 이 결과값을 출력할때 예를들어 보너스에 있는 값들의 소숫점자리 수라던가, 테이블의 제목을 넣거나 하는등의 일을 추가적으로 할 수 있습니다. 

(1) Specifying Column Formats and Labels 

우선 default값으로는, 기존에 불러오는 데이타의 포멧이 사용되어지고요. 만약 포멧팅이나 label을 설정해주고 싶으면 LABEL =  또는 FORMAT = 이런식으로 설정하면 됩니다. 이때 SELECT 구문 다음에 설정해줘야합니다. 

예를들어, 라벨 그러니까 변수 이름을 새롭게 설정해주고 싶으면~ 

SELECT empid label = 'Employee ID', 

             jobdode label = 'Job Code', 

             salary, 

empid는 불러오는 데이터에 있는 변수이름이고요. 이 변수이름을 Employee ID라고 바꾸고 싶으면 label을 사용하면 됩니다. 자세한 코드는 다음과 같아요. 

특정 열의 값의 포멧을 바꾸고 싶다면 다음처럼 지정해주면 됩니다.

Bonus를 새롭게 만들고, 이때 포맷을 설정해주고 싶으면 SELECT구문 안에서, 새롭게 만든 열 이름 다음에 format = 을 이용해서 지정하면 됩니다. 

(2) Specifying Titles and Footnotes 

아웃풋에 제목이나 풋노트를 넣고싶다면 다음처럼 사용하면 됩니다. 이때 가장 중요한건 위치!! 인데요. 이 명령어를 어디에 둬야하는지가 제일 중요합니다. PROC SQL 다음에 TITLE이용해서 제목을 지정합니다.     

위에 코드보면 제목 두개 넣고 empid, jobcode의 열 이름을 바꿨고요. format을 이용해서 Bonus의 포맷도 바꿔줬습니다. 그래서 결과값은 다음과 같아요. 변수이름 바뀌었고, Bonus는 $사인 생기고 소숫점 두자리까지 표시 되었네요. 

만약 아래 결과 그림처럼 새로운 열을 만들어서 Bonus라는 열의 이름을 없애고 대신 각 행마다 bonus is 라고 넣고 싶다면 어떻게 하면 될까요. 

아래 코드처럼, SELECT구문에서 변수로 넣고자하는 열을 지정해주고요. 

그 다음 열에 넣을 문구를 따옴표 안에 넣어줍니다. 'bonos is;', 라고 하면 위에 아웃풋처럼 Salary열 그 다음 열에, 각 행마다 bonus is: 라는 구문이 들어갑니다. 콤마를 넣어줬기때문에 salary*.10값은 그 다음 열에 들어가게 되는거고요. 

 # sas 자격증, SAS advaced, SAS advanced programming, SAS prep guide, 자격증 

Chapter 2. Performing Advanced Queries Using PROC SQL (3) 

Understanding How PROC SQL Processes Calculated Column

SELECT 구문을 통해 새로운 열(column)을 지정할 수 있는데요. 아래 예문을 봅시다~ 

sasuser라이브러리에 있는 marchflights데이타에서 flightnumber, date, destination 열을 불러오고요. 그리고 boarded, transferred, nonrevenue의 열을 합친걸 Total이라는 이름으로하는 변수또한 만들려고 합니다. 그러니까 총 4개의 열을 가진 데이타가 아웃풋으로 출력되겠네요. 

그러면 에러메시지가 SAS log파일에 뜨게 됩니다. "The following columns where not found in the contributing tables: total." 이렇게요. 왜 에러가 뜨냐면 Total은 아웃풋으로 새롭게 만들 변수 이름입니다. 그래서 marchflights에는 Total이란 이름을 가진 열이 없어요. 그런데 저 위에처럼 코드를 입력하면 sasuser라이브러리에 있는 marchflight테이블에서 열의 이름이 Total을 찾아서 그 값의 100이하인걸 출력하려고 합니다. 그런데 marchflight테이블엔 Total이란 이름을 가진 열이 없어서 에러메시지가 뜨는겁니다. 

새로운 열을 만들고 그 열의 조건문을 걸고 싶을때!!! CALCULATED!! 라는 키워드가 필요합니다. 

Understanding  the Keyword CALCULATED!! 

calculate라는 키워드를 입력해서 수정된 SAS코드는 다음과 같고요. 이 코드를 돌려보면 아래처럼 결과값이 출력됩니다. 

# sas 자격증, sas advanced, sas advanced programming, sas prep guide 

Chapter 2. Performing Advanced Queries Using PROC SQL  (2)

Subsetting Rows By Using Conditional Operators

PROC SQL 쿼리에서 원하는 값을 결과값으로 출력하기위해서 WHERE구문을 이용해 조건을 설정할 수 있습니다. 아래 이미지에서 예를 든것처럼, 변수의 값 중에서 GOLD라는 내용이 들어간 값만 출력한다던가, 변수 값이 특정 값 이하라던가 연결문자들을 수정하던가.. 그런게 해당됩니다. 

Using Operators in PROC SQL 

SAS PROC SQL에서 사용하는 Conditional Operator로 아래와 같습니다. 

Operator와 예문을 유의하게 잘 확인해보세요. 콤마가 있는지 등등이요. ANY, ALL, EXISTS를 제외한 나머지 오퍼레이터들은 다른 SAS 구문에서도 사용할 수 있습니다. 

1) Using the BETWEEN - AND Operator to Select within a Range of Values  

위에 든 오퍼레이터 예 중에, Between-And 오퍼레이터를 보려는데요. numeric이나 character 값 중에서 특정 범위를 선택하고자할때 WHERE구문 중에서 BETWEEN - AND 연산자를 사용하면 됩니다. BETWEEN value 1 AND value 2 인데 이때 value 1 과 value 2 는 범위의 양쪽 끝 값이 되어야겠지요. 이때 value 1 과 value 2는 범위에 포함됩니다. 범위가 특정 범위 밖의 값이라고 할때 not between을 사용하면 되겠죠. 

2) Using the CONTAINS or ? (Question mark) Operator to Select a String

위에 든 오퍼레이터 예 중에, CONTAIN 또는 ? 연산자를 보려고 합니다. Contain이 포함되다란 뜻을 가지고 있잖아요. 그래서 이 연산자의 기능은, 행을 출력하고자할때 그 행에 해당하는 열(column)에 특정 단어가 포함되어있을 경우를 말합니다. 다시말해 특정 단어가 있는 행을 출력하는 기능을 갖습니다. 아래 예를 보면~ 

name이라는 변수이름을 갖는 결과값을 출력하려고 하는데요. 이때 결과값은 outobs=10 즉 10개만 출력하려고 해요. 데이터는 sasuser 라이브러리에 frequentflyers라는 데이터에서 naem의 변수의 값이 "ER" 이 들어가있는 경우만 출력하려고 합니다. 결과값 보면 Name이라는 변수의 값이 ER이 들어가있죠. 10개의 행으로 이뤄져있고요. 

3) Using the IN Operator to Select Values from a List 

특정 범위 (range)가 아니라 특정 지정된 값들 중에서 포함된걸 찾고자 할때 IN연산자를 사용합니다. 예문을 보는게 더 이해가 빠르겠죠. where jobcategory in ('PT', 'NA', 'FA') 예를 보면 jobcategory의 변수안에 들어있는 값 들 중에서 PT, NA, 또는 FA라는 값이 있는 경우, 이렇게 문자값을 설정할 수 있고요. 아니면 in (2, 4, 6) 이렇게 숫자값 2, 4, 6이 포함된 행처럼 숫자값을 설정할 수 있습니다. 아니면 not in 을 이용해서 이 값이 아닌 경우~로 설정할 수 있습니다. 

4) Using the MISSING or IS NULL Operator to Select Missing Values 

Missing Value (실측값)을 찾을때 IS MISSING 혹은 IS NULL 을 이용하면 됩니다. 

WHERE 변수이름 is missing; 이렇게 적으면 되는데요~ 

WHERE 변수이름 = .  (수치형일때)

WHERE 변수이름 = ' ' (문자값일때) 이렇게 적어도 괜찮습니다. 

하지만 IS MISSING 으로 사용하는게 더 편한 이유는 수치값 / 문자값 구분없이 다 사용할 수 있기 때문입니다. 

5) Using the LIKE Operator to Select a Pattern 

LIKE 연산자를 통해 패턴을 지정할 수 있습니다. syntax는 아래와 같고요. 바로 예문으로 들어가서 볼게요. 

대소문자 구분 하고요. 

LIKE 다음에 ' ' (quotation marks) 따옴표가 꼭 들어있어야합니다. 

그리고 _ (underscore) 나 % (percent sign) 이 따옴표 안에 들어가있어야해요. 

이때, _ 기능으로는 any single character 의 의미로, % 기능으로는 sequence of zero 또는 여러개의 캐릭터 를 의미합니다. 예문보면 바로 이해 가겠죠. 

아래 Diana, Diane, Dianna, Dianthus, Dyan이라는 값이 있고요. 오른쪽 열의 원하는 값을 출력할때 왼쪽의 Pattern을 사용하면 됩니다. 

만약 WHERE address LIKE '% P%PLACE'; 인 경우, 

처음 % 이 들어가있어서 any number 갯수 상관없이 앞에 characters가 들어가있고요. 

그 다음에 빈공간이 들어가고 

그 다음 무조건 P 대문자가 있어야하고 

그 다음 % 이 들어가있어서 갯수 상관없이 문자가 들어가있고요.

그 다음 단어 PLACE가 들어가있어야합니다. 

P로 시작하는 단어의 PLACE값이 출력하겠네요. 

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

인터넷 사이트 차단 앱 - SelfControl (mac) 

페이퍼쓰는데 우와 커뮤니티, 네이버, 이메일,..이렇게 한바퀴만 돌아도 누가 시간 삭제했어요? 30분 후딱가는거?? 30분에서 놀다보면 1시간. 아주 시간 쑥쑥 잘도 갑니다. 이때 필요한건 인터넷 사이트 차단. 극단의 조치가 필요해요. 안그러면 계속하니까ㅠㅠ 이거 한번 시작하면 노트북 던져버려도 절대 풀리지 않는다고 해서 다운받았습니다. 

논문쓸때, 과제할때, 일할때 다른 인터넷 사이트 접속하면서 논다?? 싶으면 무조건 다운받으세요. 두번 설치하세요. 그러면 울면서 해야할 일 하게 됩니다!!!! 전 집에서 했더니 폰 가지고 침대에서 뒹굴뒹굴 놀길래, 도서관가서 폰은 가방에 쏙 집어놓고, 이거 설치하고 한숨쉬면서 페이퍼쓰니까 결국 페이퍼 쓰게 되더라고요. 강추합니다!!  

 SelfControl 구글검색해서 다운 받으세요. 두번 받으세요. 

다운받고 Blacklist에 차단하고 싶은 인터넷 사이트 다 적습니다. 전 학교 이메일과 구글빼고 다 집어넣었어요. 페이스북, 네이버 이런곳은 당연히 안되고요. 자주 들어가는 매거진 사이트? 이런것도 다 넣었어요. 

전 쫄보라서 3시간도 후덜덜덜 하면서 클릭했습니다. 중간에 집중 흐트러질때 네이버 접속하니까 접속안된다고 뜨네요? 다시 한숨쉬고 또 페이퍼쓰고, 한숨쉬고 페이퍼쓰고. 그래도 이러게나마 억지로 집중해서 결국 페이퍼 쓰긴 다 썼습니다. 아오.. 

기능 간단하고, 효과 확실하고~ 그래서 강추합니다!!!  (단점으로는~ 컴퓨더 대신 폰으로 인터넷하면서 놀 수 있다는거~~)