Proc sqlを極める(6)　ーCross joinー

proc sqlの第6回目です。今回はCross joinについてです。cross joinはmergeステートメントではできないので、この結合操作が必要な場合はproc sqlは必須となります。

Cross joinとは

Cross joinとは結合するテーブルのすべてのレコードの組み合わせを出力します。交差結合、クロス結合、デカルト積などと呼ばれます。

proc sqlではcross join句で実現できます。

結合後のテーブルは2つのレコード数の積となるため、レコード数が膨大になります。処理が重くなってしまうのでwhere句で条件を指定して
余計なレコードを発生させないようにしたほうが良いでしょう。

試しに以下のテーブルaとbをcross joinしてみます。

データセットa

データセットb

create table cross as 
select a.*, b.* 
from a cross join b;

結合結果は以下のようになります。テーブルaとbのレコードの全ての組み合わせが出力されます。

活用例

cross joinの利用方法としては一般的にはマスターテーブルの作成が挙げられます。

例えばproc freqで集計する際、集計対象の区分のうちデータセットに含まれない区分の集計結果は表示されませんが、集計対象の区分の組み合わせを格納したマスターテーブルを作成しておけば、集計結果とマスターテーブルをマージすることですべてのカテゴリの集計結果を表示することができます。

以下の図式ですと区分変数が一つだけなので簡単ですが、区分変数が複数ある場合はcross joinをつかうと簡単にマスターテーブルを作成できるでしょう。

もっとも区分は大抵の場合コード値として連番を振っているケースが多いのでdoループで作成することもできます。必ずしもcross joinが必須というわけではないですね。

もう一つ実用例をご紹介しましょう。

各被験者の検査日(lbdt)を格納したデータセットadlbと、各被験者毎に定められた一定期間(開始日stdt, 終了日 endt)を格納したデータセットintervalがあります。

intervalの各レコードに記載されている期間内で実施された検査の回数を集計したいときどのようにすればよいでしょうか？

intervalから期間の開始と終了を取得し、この範囲にあるlbdtをカウントすればよいわけですから、期間の開始と終了を引数として、adlbから該当するレコードをカウントするマクロを
作成し、データセットintervalを使ってcall executeでマクロを実行する方法を思いつく人もいるかもしれません。

しかしこの方法だとcall executeが実行されるたびに集計結果を新しいデータセットかマクロ変数に格納するしかないため、被験者数が多いと莫大な数のデータセットが作成されてしまいます。

dosubl関数を使えばメインルーチンに集計結果を返すことができますが、dosubl関数は実行速度がめちゃくちゃ遅いので被験者数が多い場合は現実的ではありません。

そこでcross joinの出番です。まずadlbとintervalをcross joinします。同一被験者同士で結合したいため、where句に条件を追加します。

こうすることでinvervalの個々のレコードにadlbのすべてのレコードを結合することができます。

あとはlbdtがintervalの期間内にある場合にカウントアップすれば、各期間の最終レコードに集計結果が算出されます。

*同一被験者のレコード同士でcross join; 
*intervalの各レコード毎にadlbをまるごとくっつけるイメージ; 

proc sql noprint; 
create table count_date as 
select interval.*, 
       adlb.lbdt 
from interval cross join adlb 
where interval.usubjid=adlb.usubjid 
order by usubjid, seq, lbdt; 
quit; 

data count_date2; 
set count_date; 
by usubjid seq; 
retain lbcount; 

if first.seq then lbcount=0; *期間内のlbdtをカウントアップ; 
if stdt<=lbdt<=endt then lbcount+1; 
if last.seq; 
drop lbdt; 
run;

集計結果は以下のようになりました。

正しく集計できたか検算してみましょう。

あってますね！

このようにcross joinが使えると圧倒的に楽になる集計もありますので、sqlはちゃんと習得しましょう。