[SAS 프로그래밍 고수 백승민] PROC SQL에서 중위수(MEDIAN) 구하기

[#1]

data back;
     input VAR1 VAR2 VAR3;
cards;
1 2 3
3 4 5
3 7 8
5 4 1
4 3 2
6 3 2
10 1 4
;

* 1. PROC SQL의 MEDIAN은 각 관측치의 중위수(MEDIAN)를 계산;
* http://support.sas.com/kb/12/133.html;
PROC SQL;
  SELECT MEDIAN(VAR1,VAR2,VAR3)
  FROM   BACK;
QUIT;

* 2. 중위수 구하기;
PROC SUMMARY DATA=BACK;
     VAR _NUMERIC_;
     OUTPUT OUT=BACK2(DROP=_:) MEDIAN=;
RUN;
PROC PRINT;
RUN;

* 3. PROC SQL에서 중위수 구하기
  - Celko's Median : http://www.sascommunity.org/wiki/Celko's_Median
                     http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0003b&L=sas-l&P=31590;

proc sql;
   select avg(distinct var1) AS VAR1_MEDIAN
   from   (select f1.var1
           from   back as f1,
                  back as f2
           group  by  f1.var1
           having sum(case when f2.var1 = f1.var1 then 1
                           else 0
                      end)
                  >=
                  abs(sum(case when f2.var1 < f1.var1 then 1
                               when f2.var1 > f1.var1 then -1
                               else 0
                          end)));
quit;

* 4. 자기자신을 제외한 중위수 구하기;
*  - 질문 및 테스트 데이터(마들렌12님) : http://cafe.daum.net/statsas/B3m/10708
*  - 답변(최홍규님) : cafe391.daum.net/_c21_/cafe_nsread?grpid=5G5&fldid=B3m&contentval=002mi002mrzzzzzzzzzzzzzzzzzzzz&datanum=10717&searchlist_uri=%2F_c21_%2Fcafesearch&search_ctx=VnKaQtKZsJZB2Q6rEnyTQNJLYJ1BLByrXH6apwnmUgOL5YegifEsdzavpsGiEH5xO.xRaYh_nUbgdU2l4dB2iKxkTzVxPyWhlzHgdGoifUCT5IeF_ZQGkt5u_h5Pnvf3.VX8QiciFsskCleKlwqBjybwCltKxTMhkZ9lj5te913NV6Z9sgzoz.e5RWPQ-D14zaK_rjB4D-Z_KcOHdkWXqtDEnwTIYRUL_Me-eaVkjj6WronaTio6lAPL.XcmQlkzotSXcK-FsnE0

* Celko's Median 참고예제;
data parts;
   input @1  pno    $2.
         @4  pname  $5.
         @10 color  $5.
         @16 weight 2.
         @19 city   $6.;
cards;
p1 Nut   Red   12 London
p2 Bolt  Green 17 Paris
p3 Cam   Blue  12 Paris
p4 Screw Red   14 London
p5 Cam   Blue  12 Paris
p6 Cog   Red   19 London
;;;; *****; run;
 
proc sql;
   select avg(distinct weight)
   from   (select f1.weight
           from   parts as f1,
                  parts as f2
           group  by f1.pno, f1.weight
           having sum(case when f2.weight = f1.weight then 1
                           else 0
                      end)
                  >=
                  abs(sum(case when f2.weight < f1.weight then 1
                               when f2.weight > f1.weight then -1
                               else 0
                          end)));
 
*****; quit;