Rolling joinとかをSASで考えてみる

Rolling Joinについて真剣 に考えてみる イーピーエス株式会社 森岡裕

『名前をつけてやる』は、 日本のロックバ ンド・スピッツの2作目のオリジナルアル バム。1991年11月25日 名盤！！ 「名前をつける」＝存在を肯定する行為 正体不明で不器用な感情や存在を、排除せず、笑わず、「それでもいい」 と認めてあげること

例えばAにBから，IDとTIMEをkeyとして，B.VALの値を取得したい 場合を考える ID=1のTIME=10 やID=2のTIME=2,5などはAにもBにも存在する値 なのでSQLでいうLEFT JOINで簡単にVALを取得できるだろう

ID=1のTIME=2について，データセットBには「マッチするIDの中でTIMEでマッチする値が存在しな い」 同値のTIMEが存在しない場合に，TIMEにおいて，過去方向で最も近い値をもつレコードをマッチする 上記の場合であれば，2より過去で，最も大きい値は1なので，1でマッチさせる このように特定の結合キーのある変数が完全一致しない場合に，条件に当てはまるレコードを代わりに マッチさせる結合について考える

IDが完全一致する中で，A.TIME<=B.TIMEとなる最大のTIMEを持つレコードをマッチする Bに同TIMEが重複して存在する場合は元のオブザベーションが先の方をとる という処理を実装すると，結果は右のようになる

だけど その処理には名前がなかった

その処理には名前がなかった はっきり言って ありふれた条件付き結合処理です． 時系列データ処理の実務的要請から段階的に形成され、後に名前が与えられた概念 ありふれた処理概念が名前を獲得することでどのようになるのかを歴史的流れ に沿って整理します

1. 時系列データ処理の黎明期（1960–80年代） 本質的な課題 •「ある時点の観測値に対し、直前（または最近）の情報を結び付けたい」 •金融、製造、臨床、センサーデータで普遍的に発生 当時の実装 •明示的な概念名は存在しない •以下のような処理として実現されていた： • Fortran / COBOL での逐次走査 • 「最後に見た値を保持する」ロジック • 手続き型の stateful processing ポイント！ この段階では「join」ではなく「時系列補完・参照」として扱われていました。

2. RDB・SQL時代：非等価結合としての発展（1980–90年代） SQLにおける前身概念 •Non-equi join（非等価結合） SELECT * FROM A JOIN B ON B.date <= A.date 問題点 •結果が多対多になりやすい •「直前1件」を選ぶには： • サブクエリ • GROUP BY + MAX • 相関サブクエリ 論理的には可能だが、非効率・冗長 この時点で： •「時点Aに対し、Bの最新レコードを結合する」というパターンは確立 •しかし名前も抽象化もまだ不十分

3. SAS・統計処理系での定式化（1990–2000年代） SAS DATA step における考え方 •BY-group processing •retain / lag / last.変数 •hash object（2000年代） 例（概念的）： •Visit日に対し、直前の投与量を保持 •SDTM/ADaM で頻出 ここでも： •「XXXXXX」という特定の名称は使われない •しかし実務的には完全に同一の処理 特に臨床領域では： •「as of date」 •「most recent prior」 という言い回しが主流

4. data.table による命名と概念の明確化（2010年前後） 転換点：R / data.table Matt Dowle による data.table 開発（2006–） Rolling Join の明示的導入 •roll=TRUE •roll=Inf •roll=-Inf 例： DT2[DT1, on="date", roll=TRUE] ここで起きたこと •それまで暗黙的だった処理が： • joinの一種として定義 • rolling（転がす）という直感的比喩で命名 •「等価結合＋補正」ではなく 独立したjoin操作として概念化 この時点で初めて： •rolling join = 時系列最近傍結合 という認識が広まる

Rolling Join - data.table Rolling join is an ordered join that fills in matches using the closest previous (or next) key value, when an exact match is not found. For each value in the query table, find the closest matching key in the source table that does not exceed it (when roll=TRUE). In data.table's Rolling Join, when there are duplicate key values,the row that is matched depends on the mult argument:by default, the first matching row is used (mult = "first"),while setting mult = "last" makes it use the last one. Option Description roll = TRUE Carry the most recent past value forward (LOCF) roll = Inf Carry the most recent past value forward (LOCF) roll = -Inf Carry the next future value backward (NOCB) roll = "nearest" Use whichever value is closest (past or future) Copyright©EPS All rights reserved. 13

5. その後の一般化・他言語への波及（2015年以降）） SQL •Window関数： • last_value() • row_number() over (partition by … order by …) •LATERAL JOIN •ASOF JOIN（ClickHouse, DuckDB） Python / pandas •merge_asof() • 明示的に as-of join と命名

概念史のポイント 1.処理は昔から存在 2.名前と抽象化が後から追いついた 3.data.table が初めて，名前を与え，joinとして定義し高速実装を提供した 一言で言うと Rolling join は 時系列処理の職人芸を、宣言的データ操作に昇華した概念

そんなRolling Joinに森岡なりに敬意を表して

Rolling Joinを，森岡なりにRolling Matchとして再定義 Rolling Matchは以下の機能を持つ ①BACK⇒完全一致しない場合は過去方向に走査して最も近い値でマッチする ②FORWARD⇒完全一致しない場合は未来方向に走査して最も近い値でマッチする ③NEAREST⇒完全一致しない場合は過去未来方向に走査して最も近い値で マッチする Rolling Matchは以下の特性を持つ ・JoinではなくMatchであるため，1対多をによるレコード拡大を認めない ・走査の許容幅を設定可能，たとえばBACKなら，過去3日以内の差をマッ チ対象にするなど，NEARESTなら -3日から＋5日までといった指定も可能 ・ NEARESTについて，たとえば，-3日と＋３日は起点から等距離であるが その際に前を採用するか，後を採用するかは，任意に指定可能とする

data C;
set A;
if 0 then set B;
if _N_ = 1 then do;
length ___row_number ___TIME 8.;
call missing(of ___row_number ___TIME min_diff hash_diff target_row_number);
rc=dosubl("data htemp0/view=htemp0;set B curobs=_cur; cobs=_cur;run;
proc sql noprint;
create view htemp as select *, cobs as ___row_number,time as ___time from htemp0
order by ID, TIME;
quit;"
);
declare hash h1(dataset:"htemp",multidata:"Y");
h1.definekey("ID");
h1.definedata("___TIME","___row_number");
h1.definedone();
declare hash h2(dataset:"htemp");
h2.definekey("___row_number");
h2.definedata("VAL");
h2.definedone();
end;
call missing(of ___row_number ___TIME);
call missing(min_diff, target_row_number);
do while(h1.do_over()=0);
if n(of TIME ___TIME) =2 then hash_diff = TIME - ___TIME;
if ^missing(hash_diff) and 0<=hash_diff then do;
if missing(min_diff) or hash_diff <= min_diff then do;
if hash_diff =min_diff and ___row_number < target_row_number
target_row_number =___row_number;
end;
else do;
target_row_number =___row_number;
end;
min_diff = hash_diff;
end;
end;
end;

___row_number=target_row_number;
if h2.find() ne 0 then do;
call missing(of VAL);
end;
drop ___row_number target_row_number rc min_diff hash_diff ___TIME;
run;

たとえばBACKを，平コードで
実装すると右のようになる
肝なのは，マッチするレコー
ドの特定と

then do;

そこから変数を取得する部分
のハッシュオブジェクトを2つ
にわけていること

一度，取得元に対して オブザベーション番号を付与した Viewをdosubl関数で作り それをハッシュにいれて マルチキーで定義して Do_overメソッドでもっとも近い 地点のオブザベーション番号を 特定する． そしてそれをキーにして 別途値を取得する

万人が使える形に実装してこそ意味が生まれるので マクロ化しました

%rolling_match( Master=, Key=, Rollvar=, Rolltype=BACK, Roll_back_limit=, Roll_forward_limit=, Var=, Wh=, Nearest_tie_dir=BACK, dupWARN=Y);

•master=（必須） 検索対象となるマスターデータセット（例：B）。 このデータセットからマッチした値が取得されます。 •key=（必須） マッチンググループを定義する 1 つ以上のキー変数（スペース区切り）。 例：key=ID ／ key=STUDYID USUBJID ／ key=SITEID SUBJID VISITNUM •rollvar=（必須） ローリングロジックに使用される時間型変数。通常は数値型（例：datetime、date、visit day、time）。 現在の DATA ステップのデータセットおよびマスターデータセットの両方に存在している必要があります。 •rolltype=（任意、デフォルト=BACK） ローリングの方向／戦略。指定可能な値：BACK、FORWARD、NEAREST。 -- BACK 現在値以前で最も近い値を選択します（master.rollvar <= current.rollvar）。 距離は diff = current.rollvar - master.rollvar（diff >= 0）として定義されます。 -- FORWARD 現在値以後で最も近い値を選択します（master.rollvar >= current.rollvar）。 距離は diff = current.rollvar - master.rollvar（diff <= 0）として定義されます。 --NEAREST 前後いずれの方向でも最も距離の近い値を選択します（abs(diff) を最小化）。 過去と未来の候補が同距離の場合は、nearest_tie_dir= で優先方向を制御できます。

•roll_back_limit=（任意、デフォルト空＝制限なし） BACK および NEAREST における最大の過去方向距離。 diff = current - master（diff >= 0）として解釈され、 0 <= diff <= roll_back_limit を満たす候補のみが対象となります。 •roll_forward_limit=（任意、デフォルト空＝制限なし） FORWARD および NEAREST における最大の未来方向距離。 diff = current - master（diff <= 0）として解釈され、 -roll_forward_limit <= diff <= 0 を満たす候補のみが対象となります。 •var=（必須） マッチしたマスターレコードから取得する 1 つ以上の変数（スペース区切り）。 これらの変数は現在の DATA ステップの PDV 上で作成または上書きされます。 例：var=VAL ／ var=VAL FLAG ／ var=LABVAL LABUNIT •wh=（任意、デフォルト空） 内部参照ビュー作成時にマスターデータセットへ適用される WHERE 条件。 %nrbquote() などのマクロマスキング関数と併用することを前提としています。 条件は DATA ステップの WHERE 文にそのまま挿入されます。

実行例

data A; ID=1;TIME=-1;output; ID=1;TIME=2;output; ID=1;TIME=5;output; ID=1;TIME=10;output; ID=2;TIME=2;output; ID=2;TIME=5;output; ID=2;TIME=10;output; ID=2;TIME=16;output; run; data B; ID=1;TIME=4;VAL="B";output; ID=1;TIME=1;VAL="A";output; ID=1;TIME=6;VAL="C";output; ID=1;TIME=10;VAL="D";output; ID=2;TIME=2;VAL="E";output; ID=2;TIME=5;VAL="F";output; ID=2;TIME=10;VAL="G";output; ID=2;TIME=10;VAL="H";output; run;

data C1; set A; %rolling_match( master=B,key=ID,rollvar=TIME, rolltype=BACK,var=VAL ); run;

data D1; set A; %rolling_match( master=B,key=ID,rollvar=TIME, rolltype=FORWARD,var=VAL ); run;

data E1; set A; %rolling_match( master=B,key=ID,rollvar=TIME, rolltype=NEAREST,var=VAL ); run;

https://github.com/PharmaForest/mergex PharmaForestのmergexパッケージ内に Rolling_matchマクロとして公開していて 使用例もかなり豊富に書いたので見てみてください

• https://github.com/PharmaForest/mergex

まとめ 統計解析理論ができるのがエライと思って，データハンドリングを軽視してる人は総じてクズである データハンドリングの理論も立派な学問分野であり，結合について考えることも学問である 日常的に行っている名もなき処理について，今一度改めて考えて，定義する 定義した後，再度，実装してみると，そこから新しい分野が開花したりする． つまり， 「名前をつけてやる」という態度は 世界を肯定し，前進させる力である