推 FallAugust:十分感恩! 我終於不用癡癡等迴圈了 01/03 20:32
※ 引述《FallAugust (八月秋風)》之銘言：
: 以下真的受益匪淺，有一個延伸的問題請教
: 因為有的時候配到最適合的那個對照公司因為資料可得性不能用，
: 這時候就要找第二順位了，
: 之前我用macro作配對的時候，可以利用排序和 _n_ 的設定
: 留下最相近的五筆資料備用，或者作match-portfolio
: 想請問在sql裡面如果想留下最相近的五筆配對資料，應該要怎麼下指令呢?
: 謝謝
分兩段跑吧
proc sql;
create table controls_id as
select
one.ID as study_id,
two.ID as control_id,
one.TA as study_TA,
two.TA as control_TA,
one.Sales as study_sales,
two.Sales as control_sales,
one.rand_num as rand_num,
abs(one.ta-two.ta) as diff
from study one, control two
where (two.TA between one.TA*1.1 and one.TA*0.9)
order by one.id, diff;
quit;
data new;
retain flag;
set controls_id;
by study_id;
if first.study_id then flag=0;
flag+1;
if flag<=5;
drop flag;
run;
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 140.112.117.131