Proc sqlを極める(9)　ーnレコード前、nレコード後のデータを取得するー

前回proc expandを使った記事をアップしたところ、SASライセンスの契約内容によってはこのテクニックは使えない人がいるとのご指摘を受けました。

superman-jp.hatenablog.com

であればproc sqlのほうがいいかなと思い、追加でアップします。

SAS公式で紹介されている方法

一部のSQL製品やRのtidyverseではnレコード前のデータを取得するlag関数と、nレコード後のデータを取得するlead関数が実装されています。

SASで同じ処理を行う方法についてやはり問い合わせがあるのか、SAS公式からも実施方法が公開されています。

support.sas.com

support.sas.com

適切なキー変数を作成してマージするか、setステートメントのpointオプションでデータの配置先を指定するかの２択のようです。
複雑な条件を追加したいのであれば素直にキー変数を作成してマージが正解かな。

pointオプションというのは私全然使ったことなかったので、SAS公式で紹介されている方法を参考に前回の記事と同じ処理を書いてみました。

Copy

data out_sas;
set test;
by usubjid;
*セットするオブザベーション番号を作成する;
lbseq_lag = _N_- 1;
lead_pt = _N_ + 1;
if first.usubjid then do;
set test(keep=aval rename=(aval=aval_lead)) point=lead_pt;
aval_lag=.;
end;
else if last.usubjid then do;
set test(keep=aval rename=(aval=aval_lag)) point=lag_pt;
aval_lead=.;
end;
else do;
set test(keep=aval rename=(aval=aval_lead)) point=lead_pt;
set test(keep=aval rename=(aval=aval_lag)) point=lag_pt;
end;
run;

一応できましたが、pointオプションはオブザベーション番号でしかsetする位置を指定できないのはちょっと微妙かな

ユーザーが作成したキー変数でset位置を指定できるのならいいけど、それやるならマージでやれって話ですよね・・・
今回は単純にオブザベーション番号に基づいてデータを取得しているのでこのやり方でもOKですが、条件を追加するのは厳しい気がします。

SQLを使う方法

proc sqlにはlag関数やlead関数は存在しません。そもそもSQLはレコードの順序というものは存在しない設計なので、今回のケースだとテーブルの結合で実施することになります。

SQLの良いところは結合条件が柔軟に指定できる点です。

データステップのmegeステートメントは変数名が一致していないといけませんが、SQLは変数名が一致していなくても結合可能ですし、
結合キーの値から導出した結果をもとに結合することも可能です。

先ほどのプログラムと同等の処理はSQLではこのように書くことができます。

Copy

proc sql noprint;
create table out_sql  as
select a.*,
b.aval as aval_lead,
c.aval as aval_lag
from test as a
/* １行先の測定値を取得 */
left join test as b
on a.usubjid = b.usubjid and
a.lbseq + 1 = b.lbseq
/* １行前の測定値を取得 */
left join test as c
on a.usubjid = c.usubjid and
a.lbseq - 1 = c.lbseq;
quit;

lbseqはusubjid毎の連番なので、lbseqに1を足せば１レコード後のデータを、1を引けば１レコード前のデータを参照できます。

on句では計算結果も条件に追加できますので、キー変数を作成しなくても同様の処理ができます。

データ取得条件を追加したい場合はon句に記述された結合条件を修正すればよいでしょう。

先ほどのpointオプションでの実施例とは異なり、条件分岐で欠損を代入するといった操作も不要です。

proc sqlはbase SASだから基本的に全ユーザー使えるし、汎用性という意味ではproc expandよりも優れていると思います。