whereステートメントで処理時間を遅くする書き方～年月でオブザベーションを絞り込む～

whereステートメントで処理時間を遅くする書き方～年月でオブザベーションを絞り込む～ - SAS

- 6月 21, 2015

年月でオブザベーションを絞り込む方法について質問を受けたとき、処理効率の話になったので、その話をまとめてみました。

オブザベーションを条件式で絞り込む方法には、whereステートメントとサブセット化 ifステートメントがあります。基本的には、whereを使ったほうが処理速度が早いのですが、書き方によっては if 並に遅くなってしまいます。

下記に、2015年6月のデータのみに絞り込むための 5つのサンプルパターンを作成しました。

年月で絞り込むテストコード

options lognumberformat ; /* ログのオブザベーション数にカンマを付ける */

/* テストデータ */
data TEST ;
  format DT yymmdds10. ;
  do I=1 to 100000000 ; /* 1億 */
    /* 2015/01/01 から 365日間のいずれかのSAS日付を項目 DT に格納します  */
    DT = mdy(01, 01, 2015) + int(365 * ranuni(1)) ;
    output ;
  end ;
run ;

/* 2015年6月に絞り込むためのマクロ変数を定義します */
%let TARGET_MONTH = %sysfunc(mdy(6, 1, 2015)) ;
%let TARGET_YYYY  = %sysfunc(year(&TARGET_MONTH.)) ;
%let TARGET_MM    = %sysfunc(month(&TARGET_MONTH.)) ;

%put &=TARGET_MONTH. ;
%put &=TARGET_YYYY. ;
%put &=TARGET_MM. ;

/* intnx関数から来月の月初を算出して、絞り込む */
data TEST1 ;
  set TEST ;
  where &TARGET_MONTH. <= DT < intnx('MONTH', &TARGET_MONTH., 1, 'BEGINNING') ;
run ;

/* intnx関数から今月の月末を算出して、絞り込む */
data TEST2 ;
  set TEST ;
  where &TARGET_MONTH. <= DT <= intnx('MONTH', &TARGET_MONTH., 0, 'END') ;
run ;

/* between関数を用いて絞り込む */
data TEST3 ;
  set TEST ;
  where DT between &TARGET_MONTH. and intnx('MONTH', &TARGET_MONTH., 0, 'END') ;
run ;

/* year関数とmonth関数を用いて絞り込む */
data TEST4 ;
  set TEST ;
  where year(DT) = &TARGET_YYYY. and month(DT) = &TARGET_MM. ;
run ;

/* TEST1 の if版 */
data TEST5 ;
  set TEST ;
  if &TARGET_MONTH. <= DT < intnx('MONTH', &TARGET_MONTH., 1, 'BEGINNING') ;
run ;

テストコードに使用している関数の大雑把な説明

ranuni関数

0から1までの一様乱数を取得する関数。引数はseed値。
int(365 * ranuni(1)) で 0から364の整数値を取得するために使用しています。

mdy関数

SAS日付を取得する関数。第1引数から第3引数まで、それぞれ、月、日、年を指定する。個人的に、'DDMMMYYYY'd 形式でSAS日付を指定するより好きです(月の略語をすぐ思い出せないから)

intnx関数

指定したSAS日付の翌月の月末とか1週間後とか1年前とかを取得する関数。
第1引数に、年単位、月単位、週単位で計算するかを文字列で指定します。
第2引数に、SAS日付を指定。第3引数に、○ヵ月後、○週後、○年後の○にあたる数値を指定します。マイナスを指定すると、過去になります。
第4引数には、第1引数で指定した単位の初めや末などを取得するための文列字を記載します。月末や週末、月初などを取得できます。

例1. 翌月の月初 : intnx('MONTH', &TARGET_MONTH.,  1, 'BEGINNING')
例2. 前月の同日 : intnx('MONTH', &TARGET_MONTH., -1, 'SAMEDAY')
例3. 今月の月末 : intnx('MONTH', &TARGET_MONTH.,  0, 'END')

year関数

指定したSAS日付の年を数値で取得する関数。

month関数

指定したSAS日付の月を数値で取得する関数。

結果 - 処理時間 / CPU時間

	処理時間 [秒]	CPU時間 [秒]
TEST1	24.38	8.42
TEST2	25.85	8.45
TEST3	24.76	8.92
TEST4	57.77	49.28
TEST5	57.95	48.62

TEST1～TEST3 が最も早い結果となりました。それぞれの結果はあまり差が出ていません。(環境にもよるかもしれませんが、TEST3 の between関数を用いたパターンが若干遅くなることがありました)

TEST4, TEST5 は、比較して大きく処理速度に差がでました。TEST4 は、where で書かれていますが、TEST5 の if とほとんど同じ結果です。

TEST4 は、TEST1～TEST3 と違い、year関数やmonth関数で項目DTの値に対して計算を行なっています。そのため、毎オブザベーションごとに計算が行われて、処理が遅くなるという感じです。

書き方次第でwhereでも大きく処理速度に差がでることがあるので、気をつけましょう、というお話でした。

余談ですが、where先生は、Oracle やら DB2 やら Teradata やら、他のデータベースのテーブルから直接データを取ってくるときに真価を発揮します。if で書くと、テーブルの全データをデータベースからSAS側に持ってきてから絞り込みをするので、とても遅いです。また、容量オーバーの危険性もあります。where だとデータベース側で絞り込みを行なってから、SAS側にデータを持ってきてくれるので if に比べて早くなります。もしSASとデータベースが別サーバーだと、ネットワークの回線速度の問題も絡んでくるので、処理速度は大きく差が出てきます。(SASの方が絞り込みが早い,,,という環境があれば、結果は違ってくると思います)

このブログを検索

data Memorandum ; set Memory ; run ;