proc sql: 데이터 3개 merge

[안재형]

/*

데이터 3개를 만들어보죠.

2명이 첫번째 방문에 키, 몸무게를 재고, 6개월동안 운동을 열심히 한 후 다시 키, 몸무게를 재서 BMI를 계산했다고 하죠.

보통 이런식으로 쪼개서 데이터를 관리하는게 보통입니다.

demo: 1 observation per person

visit: 1 observation per person & visit

vs (vital signs): 1 observation per person, visit, & test

*/

data demo;
  input id $ birthdate is8601da.;
  datalines;
1 1974-01-09
2 1980-12-07
;

data visit;
  input id $ visitnum date is8601da.;
  datalines;
1 1 2016-01-03
1 2 2016-07-18
2 1 2015-08-28
2 2 2016-02-23
;

data vs;
  input id $ visitnum test $ result;
  datalines;
1 1 height 1.74
1 1 weight 121
1 2 height 1.74
1 2 weight 98
2 1 height 1.69
2 1 weight 92
2 2 height 1.69
2 2 weight 63
;

/* 각환자가 운동 전/후에 BMI를 보려면  

   데이터 4개를 join했는데, where을 이용해 vs는 각각 키와 몸무게로 나누었습니다. as h, as w라는 가짜 이름을 줬습니다.

   demo에 visit이 merge되고 (id로),

   visit에 키, 몸무게가 merge된걸 (id, visitnum으로) 유심히 보시기 바랍니다.

*/

proc sql;
  select demo.id,
         demo.birthdate format=is8601da.,
         visit.visitnum,
         visit.date format=is8601da.,
         w.result/h.result**2 as bmi
  from demo
    left join visit on demo.id=visit.id
    left join vs (where=(test='height')) as h on visit.id=h.id & visit.visitnum=h.visitnum
    left join vs (where=(test='weight')) as w on visit.id=w.id & visit.visitnum=w.visitnum  
  order by demo.id, visit.visitnum
; quit;

id         birthdate  visitnum        date       bmi
----------------------------------------------------
1         1974-01-09         1  2016-01-03  39.96565
1         1974-01-09         2  2016-07-18  32.36887
2         1980-12-07         1  2015-08-28  32.21176
2         1980-12-07         2  2016-02-23  22.05805

/*

vs (where=(test='height')) 이런식으로 하는건 SAS에서나 통합니다.

SQL 표준은 다음과 같이 sub-query를 만들어야합니다. 그냥 ( )안에 SQL문을 넣어주면 됩니다.

select id, visitnum, result from vs where test eq 'height'

는 하나의 독립적인 SQL 명령어입니다.

*/

proc sql;
  select demo.id,
         demo.birthdate format=is8601da.,
         visit.visitnum,
         visit.date format=is8601da.,
         w.result/h.result**2 as bmi
  from demo
    left join visit on demo.id=visit.id
    left join (select id, visitnum, result from vs where test eq 'height') as h
               on visit.id=h.id & visit.visitnum=h.visitnum
    left join (select id, visitnum, result from vs where test eq 'weight') as w
               on visit.id=w.id & visit.visitnum=w.visitnum  
  order by demo.id, visit.visitnum
; quit;

/**

이정도면 비교적 가단하지만 sub-query가 들어가 너무 복잡해지는게 다음과 같이 weight, height이 있는 새로운 데이블을 만들어 그걸 merger하는게 좋다.

**/

proc sql;

  create table hei as

  select id, visitnum, result as height

  from vs

  where test eq 'height'

;quit;

proc sql;

  create table wei as

  select id, visitnum, result as weight

  from vs

  where test eq 'weight'

;quit;

proc sql;

  select demo.id, 

         demo.birthdate format=is8601da.,

         visit.visitnum,

         visit.date format=is8601da., 

         wei.weight/hei.height**2 as bmi

  from demo

    left join visit on demo.id=visit.id

    left join hei on visit.id=hei.id & visit.visitnum=hei.visitnum

    left join wei on visit.id=wei.id & visit.visitnum=wei.visitnum  

  order by demo.id, visit.visitnum

; quit;