SASとMySQLにおける「null値」の扱い方の違いによる問題

以前に携わった案件でRDBMSとしてMySQLを使い、帳票作成やデータの更新処理はSASを通して行うシステムの開発がありました。

その際に、SASとMySQLにおける「null値」の扱い方の違いで苦しめられたことがあったので、自分の備忘録も兼ねて書いておこうと思います。

まず、SASには【In-Datebase】というサービスがあります。
これがどういったものかざっくり説明すると
『DB側で処理できる処理はDB側で処理してもらっちゃおう！』
というものです。名前のまんまですね。

パススルーを用いてSQL文を直接DB側に投げて処理させることもできるのですが、この【In-Database】のすごいところは
『SASのコードで書いた処理でも、DB側で処理した方が良い処理は、自動変換してDB側で処理させちゃう！』
ところです。賢いですね！

ただ今回の件は、この賢さが故に起きてしまった問題なのですが・・・


それでは前置きはこのくらいにして、今回起きた問題について書いていこうと思います。

例えば、以下に示すようなデータがMySQL上に存在したとします。

テーブル名「sample」
スキーマに割り当てたライブラリ参照名「db」
※四郎さんの郵便番号は「ブランク」、五郎さんの郵便番号は「null値」

上記データを「郵便番号」・「年齢」の昇順でソートしたデータをSASのワーク領域に抽出したい場合

  proc sort data = db.sample
                out  = work.sample ;
    by post_no age ;
  run ;

となります。

なんの変哲もないただのソート文なのですが・・・
SASは「null値」と「ブランク」は同じ文字値の欠損値として扱うのに対し
DB上では「null値」と「ブランク」は別のものとして扱うため
上記のソート文を実行した時に作成される「work.sample」は
【In-Database】で処理を行った場合とそうでない場合で以下のような違いが生まれます。

【In-Database】で処理していない場合

【In-Database】で処理した場合

抽出後のデータはSASデータセットなので、「null値」は「ブランク」に変換されます。
そして、注目していただきたいのは四郎さんと五郎さんの並びです！

【In-Database】を用いない場合は、DBからデータをSAS側に抽出してからソートを行うため、ソートの段階で「null値」と「ブランク」の区別はありません。

そのため、四郎さんと五郎さんの「郵便番号」は同一となり、第２ソートキーの「年齢」によって昇順にソートされます。

これに対して、【In-Database】を用いた場合は、先ほどのソートはDB側で行われて、ソートした状態のデータをSAS側に送ります。

DB側では「null値」と「ブランク」は区別され、「null値」は「ブランク」よりも更に小さい値として処理されてしまうため、五郎さんの方が、昇順で上にきてしまいます。


さて、ソート順くらい。。。と思う方もいらっしゃるかと思うのですが、、、
ここで問題発生です！！！

上記の【In-Database】を用いてソートを行ったはずのデータセットですが、並び順が本来SASでソートした順番とは違うため、DATAステップ等でグループ処理を行おうとすると、もちろんエラーになります。

つまり、以下のプログラムを【In-Database】のサービスを利用している状況下で実行するとエラーになります。

  proc sort data = db.sample
                out  = work.sample ;
    by post_no age ;
  run ;

  data work.test ;
    set work.sample ;
    by post_no age ;
    if first.age = 1 then output ;
  run ;

ログには『「work.sample」はちゃんとソートされていません。』的なログが出ます。
目の前でソートしてるのに・・・


対処法としては、「null値」と「ブランク」が混在する可能性があるデータの場合、ソート処理の前にSASデータセットとして抽出してから処理を行うことです。

もっとそもそもな方法としては
『テーブルを作成する段階で制約を設けて「null値」で登録できないようにすること』
もしくは
『「null値」と「ブランク」が混在しないようなシステムにすること』
になります。

DBのテーブル定義は思ったより奥が深く、本当に色んなことができます。
DBをただのデータの入れ物にしないように、これからも勉強を続けていきたいです。

ちなみに今回の「null値、ブランク」問題は単純なデータ抽出(where句等で)を行う際にも立ちはだかるようです。
便利なサービスも使いこなしてこそですね。