UPDATEステートメントを使ってみる

SASで結合といえば、なんだかんだいってやっぱりMergeステートメントが幅を利かせています。
書きようによって、多対多のようなケースを除いて、ほぼ全ての結合を表現できます。
Merge最高！Merge万歳！
が、場合によっては、UPDATEステートメントを使用した方が見通しがよい場合もあるので紹介します。
ちなみに僕はMergeステートメントあまり好きでありません

今データセット「MAS」と「TRA」があったとします。
UPDATEステートメントはマスタ―データセットとトランスザクションデータセットという考え方を持ち、
マスターをトランザクションで更新するといった機能になります。

data MAS;
 X=1;Y='あ';output;
 X=2;Y='ろ';output;
 X=3;Y='';output;
 X=4;Y='に';output;
run;

【MAS】

data TRA;
 X=1;Y='い';output;
 X=2;Y='';output;
 X=3;Y='は';output;
 X=5;Y='ほ';output;
run;

【TRA】

とりあえず　Ｘ　でソートします。
proc sort data=MAS;
 by X;
run;
proc sort data=TRA;
 by X;
run;



上の二つから以下の結果が欲しいとします。

変数Ｘをキーにして単純にMergeで結合するとTRAの変数Yがnullのため
結果のX=2に対応するYはnullになります。TRAにwhereでY^=''をつければいいのですが
そんなひと手間かけなくても

data UP;
 update MAS
        TRA;
     by X;
run;

で詰みです。

UPDATEステートメントはトランザクションデータセットにおいて値がnullの場合は

マスターデータセットの該当変数を上書きしないという付加機能を有するわけです。

ちなみに

data UP2;

 update MAS

        TRA updatemode=nomissingcheck;

     by X;

run;

のようにupdatemode=nomissingcheckとすると欠損値でも更新するので

すなわち

data MG;

 merge MAS

        TRA;

     by X;

run;

と同じになります。

ちなみにUPDATEと同様の表現が可能なMODIFYステートメントがいるのですが、
こいつは多機能で、深いので、いつか勉強してから紹介します。
LIBNAME　EXCELで値とエクセルにだす時以外、あんまり使ったことないので、、

ちなみに今回のケース、SQLなら

proc sql noprint;
 create table UP3 as
  select coalesce(TRA.X,MAS.X) as X
              ,coalesce(TRA.Y,MAS.Y) as Y
  from MAS full outer join TRA 
           on MAS.X=TRA.X;
quit;


こんな感じでしょうか？
僕の好きなcoalesce関数です。coalesceの順番がトランザクション→マスターなところが味噌ですか。
あとfull outer joinは両側外部結合です。どっちかにでもあればとってくるよというやつです

いずれかn個のデータセットにキー値が存在する場合のみ結合結果を残すMERGE

たとえば以下の3つのデータセットがあったとします。

【データセット Q1】
data Q1;
 A=1;B='い';output;
 A=2;B='ろ';output;
 A=3;B='は';output;
 A=4;B='に';output;
 A=5;B='ほ';output;
run;
proc sort;
 by A;
run;

【データセット Q2】
data Q2;
 A=3;C='へ';output;
 A=4;C='と';output;
 A=6;C='ち';output;
run;
proc sort;
 by A;
run;

【データセット Q3】

data Q3;

 A=1;D='り';output;

 A=3;D='ぬ';output;

 A=7;D='る';output;

run;

proc sort;

 by A;

run;

これを変数Aでマージするとき、2つのデータセットにキーが存在する場合のみマージ結果を残したいとします。

つまりA=3はQ1　Q2 Q3全てのデータセットに存在するので、対象外です。

またA=2,6,7などは1つのデータセットにしか存在しないのでこれも対象外です。

さて、どうしましょうか？

data A1;

 merge Q1(in=in1)

       Q2(in=in2)

       Q3(in=in3)

       ;

    by A;

    if in1+in2+in3=2;

run;

なんのことはないですね、上記のコードで詰みです。

in=では、そのデータセットからオブザベーションが読み込まれる時に1の値が格納されるので

足し算して2になるオブザベーションのみ残せばいいわけです。

簡単な問題ですが、in=で格納される1の値を計算式に利用して抽出するという考え方は役にたちます。

例えば実践例はあまりありませんが、

 if in1+in2*2+in3=2;

のように一部のデータセットから読み込まれる場合に重みをつけることで

特殊な条件での結合も表現できるのではないでしょうか？

メッセージレベルをiにしてMergeのミスを防ぐ

通常、SASのログに現れるメッセージと言えば「ERROR」「WARNING」「NOTE」の3種類ですが、
options msglevel=i;という風にmsglevelオプションでiを指定するとログに「INFO」というメッセージがでるようになります。

このINFOというカテゴリで表示されるようになるメッセージには、結構役に立つものが多いのですが今回はMergeステートメントによって変数が上書きされた時にでるINFOメッセージについて紹介したいと思います。

たとえば臨床試験のデータで
サイクル2,3,4の検査値データが入ったデータセットとサイクル1のデータセットがあり
この2つのデータセットから、サイクル1の検査値より低い値をとったサイクルを特定するプログラムを考えてみます。

【LB】

【LB_1】

proc sort data=LB;
 by USUBJID;
run;

proc sort data=LB_1;
 by USUBJID;
run;

data OUTPUT;
 merge LB(in=ina)
          LB_1(rename=(AVAL=BASE));
      by USUBJID;
      if ina;
      if AVAL<BASE;
run;

とつらつら書いて実行すると、もれなく大悪手です

一見正解風ですが、抽出されたデータのVISITが「サイクル1」です。
あれ？サイクル1より値の低いサイクルを特定したいのに出てきたのが「サイクル1」とはこれいかにです。

もうお気づきかと思いますが、LB_1でLBのVISITを上書いてしまっているので、
本来だしたい「サイクル2」が「サイクル1」になっちゃったわけです。
LB_1のVISITをdropすれば解決です。

初歩的なミスではありますが、結構やってしまいがちで見つけにくい類のエラーです。
特にデータマネージメントにおける論理チェック（エディットチェック・コンピューターチェック）の
プログラムを書くときは、似た様なマージ文を山ほど書くので疲れてくると僕はよくやってしまいます。

そこで以下のオプションを先頭に打ってから、同じプログラムを実行してみます。
options msglevel=i;

このように
INFO: 変数VISIT(データセット　WORK.LB)はデータセット　WORK.LB_1によって上書きされます。
というメッセージが出て、変数の上書きが起きたことがわかります。


さて、変数の上書き以外にINFOが役に立つのは、ソートプロシジャの実行アルゴリズムを教えてくれることです。ODBCでoracle等のRDBにLibnameをかけて、そのライブラリ内でソートを使うと
そのRDBのソートアルゴリズムでソートされるのですが、欠損値の取り扱いなどが通常のSASソートと違う場合があり、返された結果の意味がわからないことがありますが、そういった場合にINFOが通常のSASソートではない方法でソートされたことがわかるので解釈と対処がしやすくなります。