[SAS] SQL문

SAS에서 SQL을 사용하여 테이블을 만들거나 데이터를 조회할 수 있습니다.

---

[데이터 셋 구축]

/* LIBRARY 생성 */
LIBNAME dataset "파일경로";

/* 데이터 셋 생성 */
DATA dataset.fin;
	INFILE "파일경로/finance.csv";
	INPUT 
    gender $
    age
    Investment_Avenues $
    Mutual_Funds
    Equity_Market
    Debentures
    Government_Bonds
    Fixed_Deposits
    PPF
    Gold
    Stock_Marktet $
    Factor $
    Objective $
    Purpose $
    Duration $
    Invest_Monitor $
    Expect $
    Avenue $
    What_are_your_savings_objectives? $
    Reason_Equity $
    Reason_Mutual $
    Reason_Bonds $
    Reason_FD $
    Source $
  ;
RUN;

 

[SQL로 특정 데이터 조회]

/* SQL STATEMENT 1 : Stock_Market이 "Yes"인 사람만 불러오기 */
PROC SQL;
SELECT 
     Gender
    ,Age
    ,round(avg(Mutual_Funds),2) as Mutual_Funds, 
    ,round(avg(Equity_Market),2) as Equity_Market, 
    ,round(avg(Debentures),2) as Debentures,
    ,round(avg(Government_Bonds),2) as Government_Bonds,
    ,round(avg(Fixed_Deposits),2) as Fixed_Deposits,
    ,round(avg(PPF),2) as PPF,
    ,round(avg(Gold),2) as Gold,
FROM dataset.fin
WHERE Stock_Marktet="Yes"
;QUIT;

 

[SQL로 테이블 만들기]

/* SQL STATEMENT 2 : 성별로 금융 상품 선호도 Table */
PROC SQL;
CREATE TABLE PreferenceByGender AS
SELECT 
   Gender  
  ,round(avg(Mutual_Funds),2)     as Mutual_Funds 
  ,round(avg(Equity_Market),2)    as Equity_Market 
  ,round(avg(Debentures),2)       as Debentures
  ,round(avg(Government_Bonds),2) as Government_Bonds
  ,round(avg(Fixed_Deposits),2)   as Fixed_Deposits
  ,round(avg(PPF),2)    	  as PPF
  ,round(avg(Gold),2) 		  as Gold
FROM dataset.fin
GROUP BY Gender
ORDER BY Gender
;QUIT;

PROC PRINT DATA=PreferenceByAge;RUN;

 

[조건문 포함한 SQL문으로 테이블 만들기]

/* SQL STATEMENT 3 : 연령대 구간별 금융 상품 선호도 Table */
CREATE TABLE PreferenceByAge AS
SELECT 
   (CASE
    WHEN      age >= 40 THEN "40s"
    WHEN 40 > age >= 35 THEN "35-39"
    WHEN 35 > age >= 30 THEN "30-34"
    WHEN 30 > age >= 25 THEN "25-29"
    WHEN 25 > age >= 20 THEN "20-24"
    ELSE "Teenager"
   END) 			  as NEW_AGE
  ,round(avg(Mutual_Funds),2)     as Mutual_Funds 
  ,round(avg(Equity_Market),2)    as Equity_Market 
  ,round(avg(Debentures),2)       as Debentures
  ,round(avg(Government_Bonds),2) as Government_Bonds
  ,round(avg(Fixed_Deposits),2)   as Fixed_Deposits
  ,round(avg(PPF),2)    	  as PPF
  ,round(avg(Gold),2) 		  as Gold
FROM dataset.fin
GROUP BY NEW_AGE
ORDER BY NEW_AGE
;QUIT;

PROC PRINT DATA=PreferenceByAge;RUN;