詰めSAS12回目：キーが一致しないものを残すマージ（共通部分を除く結合）の話

タイトルで意味がわかりますでしょうか？うまく説明できません

ベン図で書くと、上の黄色の部分です（ペイントで適当に書いたベン図なので、○の大きさが違う、、）

例えば

data Q1;
do X=1,3,4,6;
 output;
end;
run;

data Q2;
do X=1,3,5,6,8;
 output;
end;
run;

のようなデータセットがあった場合に１つのデータセットにしかないXの値をもった結果

を得るには、どんなコードを書けばいいでしょうか？


ただし、MergeステートメントとSQL両方のやり方で詰ませないといけないというのが
今回の問題です。


え？第一感楽勝！と思われるかもしれませんが、案外、つまづきませんか？
実際、このタイプの結合処理って、頻度少ない気がします。

大体は両方一致するものを残すか、片方を全部残して結びつくものを残す、いわゆる内部結合や片側外部結合が大半ですよね。

実は今回も自分の解答に自信がなくて、もし間違いや、抜け穴、最適じゃない書き方であれば
ご指摘いただけると助かります。




以下、（一応）解答


【MERGEの場合】

data A1;
 merge Q1(in=ina)
       Q2(in=inb);
 by X;
 if ^(ina*inb);
run;

こんな感じでしょうか？

ifのところは (ina=1 and inb=0) or  (ina=0 and inb=1)みたいな感じでもいいですし
^(ina and inb)でも、ようはin=で指定した変数が全部1になるケースを除く書き方をしていれば
なんでもいいです。


【SQLの場合】

proc sql noprint;
 create table A2 as
  select coalesce(Q1.X,Q2.X) as X
  from Q1 full outer join Q2
       on Q1.X=Q2.X
 where ^Q1.X | ^Q2.X;
quit;


こんな感じでしょうか？
完全外部結合がみそですね。

わかりやすくするために、create tableとりはずして
where句で　Q1.X=0 or Q2.X=0　をしている部分も外しましょう（|はorを意味する記号）

proc sql;
  select Q1.X  label='Q1のX'
         ,Q2.X label='Q2のX'
  from Q1 full outer join Q2
       on Q1.X=Q2.X;
/* where ^Q1.X | ^Q2.X;*/
quit;

こうしたら

こうなるので、これをみれば、上のコードも理解しやすいはずです。
どっちかが欠測の場合を抽出しているんで、最初に見つけた欠測以外の値を取得する
coalesceが効いいるわけですね。


あ、ちなみに、僕が最初に思いついたSQLは

proc sql noprint;
 create table A3 as
  select X from Q1
   union all corr
  select X from Q2
  
  except

  select coalesce(Q1.X,Q2.X) as X
  from Q1 innner join Q2 on Q1.X=Q2.X;
  ;

quit;

です。まあ、確かにベン図を表現していなくはない、気持ちはわからなくはないと思っていただければ浮かばれます。

推理詰めSAS②coalesce関数の戻り値が元データにない？？

推理詰めSAS2回目です。

【１回目　似て非なるもの】
http://sas-tumesas.blogspot.jp/2013/12/sas.html

また今回も僕とYさんの会話から、一体Ｙさんが、どんな処理をしているのかを推理し、その上で解決法までを答えてください。

Yさん
「すみません、前に教えてもらったcoalesce関数を使って、X,Y,Zっていう3つの変数があるデータセットに対して、X,Ｙ,Ｚを順番にみて、最初の非欠損値を取得する処理を書いたんですが、、、」

僕
「エラーになった？」

Ｙさん
「いえ、エラーにはなりませんでした。WARNINGもでませんでした。だけど、結果をみるとXにもＹにもＺにも存在しない値が返されてくるんです。XにもＹにもZも0から3までしたとらない変数なのに戻り値が4とかになるんです。」

僕
「どんな風に書いたの」

Ｙさん
「以前教えてもらった、ハイフンを2つ並べて変数の格納順に一括指定する方法をつかいました」


以上。
さて、Ｙさんは一体どんなコードを書いてしまったのでしょうか？どのように直せば正常に実行できるでしょうか？








【答え】
今回は、簡単でしたね。
次に僕の言うセリフはこうですね。

「ofつけてないでしょ！！」


つまり、

data Q1;
X=1;Y=.;Z=3;output;
X=.;Y=2;Z=3;output;
X=.;Y=.;Z=3;output;
run;

こういうデータに対して

data A0;
set Q1;
 A=coalesce(X--Z);
run;

こう書いちゃったんですね。

X--ZはX+Zと同じ意味になります。ハイフンがマイナスの意味なるんですね。
マイナスのマイナスはプラスだからＸとＹを足した一つの値に対してcoalesceをかけています。
これは全く意味のない処理ですが、文法的なエラーではありません。

ただしくは

data A1;
set Q1;
 A=coalesce(of X--Z);
run;

と書いて

でした。

あと一歩、惜しかったねＹさん。

全てがnullであった時を問題とするブランクチェックについて、データ構造が縦型と横型であった場合の違い

生データをチェック・クリーニングする際に、ブランクチェックは頻度の高い処理です。

たとえば今A,B,Cという変数があって、どれか1つ以上でも値が入っていれば、問題なし。
全てが欠損値であるデータをピックアップしたいという状況があったとします。

例えばデータが

data Q1;
IDNO='001';A=.;B=.;C=10;output;
IDNO='002';A=.;B=.;C=.;output;
IDNO='003';A=5;B=.;C=.;output;
run;

こんな感じならwhereで単純に=.をandでつないでもいいし

data A1;
 set Q1;
 if coalesce(A,B,C)=.;
run;

こんな感じでもいいし、簡単です。

少し頭を使うのは、同じ意味であってもデータ構造が

data Q2;
IDNO='001';ITEM='A';VAL=.;output;
IDNO='001';ITEM='B';VAL=.;output;
IDNO='001';ITEM='C';VAL=10;output;
IDNO='002';ITEM='A';VAL=.;output;
IDNO='002';ITEM='B';VAL=.;output;
IDNO='002';ITEM='C';VAL=.;output;
IDNO='003';ITEM='A';VAL=5;output;
IDNO='003';ITEM='B';VAL=.;output;
IDNO='003';ITEM='C';VAL=.;output;
run;

こういう場合です。
1つのIDにつき項目ごとにオブザベーションが別れています。
問題にすべきはA,B,Ｃ全てが欠損である002のIDで、それを特定したいわけですが、
さて、どう書きましょうか？

まず1つとしては

proc transpose data=Q2 out=Q2_(drop=_NAME_);
 var VAL;
 by IDNO;
 id ITEM;
run;

とすれば、最初の問題のデータ構造に転置されるので、そうしてから
同じやり方で片付ける方法です。

縦に解く問題が難しければ、横にすればいいし
横に解く問題が難しければ、縦にすればいいというのはSASの定跡ですね。

まあ、でもこの場合、わざわざデータ構造を変えなくても
例えば、

proc means data=Q2 nway noprint;
class IDNO;
var VAL;
output out=A2(where=(S=.)) sum=S;
run;

このようにすれば、IDごとにAからCの合計をだしてくれるわけですが
sumによる加算結果がnullになるのはどんな時でしょうか？
それは対象全てがnullであったときのみです。その他の場合はnullを除いて足し算してくれます。
すなわちsumがnullであればA,B.Cすべてnullであることに他ならないので
上記のコードでID 002を特定できるわけです。
maxとかでももちろんいいです。


他のアプローチとして、

proc sort data=Q2;
 by IDNO VAL;
run;

data A3;
 set Q2;
 by IDNO VAL;
 if last.IDNO and VAL=.;
run;

もアリですね。

ID順、VAL順にソートして、各IDの最後のVALがnullである場合は、そこまでの全てのVALがnullであることと同義ですからね。

【訂正補足】coalesceとcoalescec

間違えました。訂正です。

UPDATEステートメントを使ってみる　の中でSQL文

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

を紹介して

数値にはcoalesce、文字にはcoalescecと書いたのですが、

それはデータステップ内で使用する場合でした。

もちろん上記コードで間違いなく実行できるのですが

SQLプロシジャ内で使用する場合、coalesce関数は本来、標準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;

のように「c」をつける必要ないのでした。

うかつでした。

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は両側外部結合です。どっちかにでもあればとってくるよというやつです

詰めSAS3回目_最初にnullでない変数の値を取得する

変数を順番にみていって、null以外が初めて出現する際の値を取得する。また全ての変数がnullである場合、特定の値を代入する。
詰めSAS3回目は少し変わった処理になります。

問題は以下のデータセット

data Q3;
length A B C D $2.;
A='';B='ろ';C='';D='に';output;
A='い';B='ろ';C='は';D='に';output;
A='';B='';C='は';D='に';output;
A='';B='';C='';D='に';output;
A='';B='';C='';D='';output;
run;

について、A→B→C→Dとみていき、nullでない最初の値、すべてnullの場合は「へ」と返し
目的局面図

のようなデータセットを作成します。



【解法1】
data A1;
length E $2.;
 set Q3;
  E=coalescec(A,B,C,D,'へ');
 keep E;
run;

第一感として、配列とdo loopで処理する方法があると思うのですが
最短最善はcoalesce関数（今回は対象が文字変数なのでcoalescec）だと思っています。
引数のうち、指定順にnullでない最初のものを返します。
もともとSQLで使われていた関数のSASへの輸入だと思いますが、こいつは非常に優秀な
関数で、横方向への順次走査のような処理を関数でできてしまいます。定数をつかえば
最終的にnullの場合に定数を与えることができます。
たとえば臨床試験で、CRFにチェックボックスがいくつかあるもので、
よく、一つでもチェックがあれば何かのフラグをたてるといった処理がありますが
これをつかえば、ifステートメントを書き連ねる必要がなくなります。
ちなみにcoalesceはコアレスと発音します。
僕はコールエッセ関数とずっと呼んでいて恥をかきました。コアレスなんて読めます？

【解法2】
data A2;
 set Q3;
  array AR{4} $ A B C D;
   do i=1 to 4;
    if AR{i}^='' then do;
    E=AR{i};
　　leave;
   end;
  end;
  if E='' then E='へ';
 keep E;
run;

配列ならこんな感じでしょうか？実は最近までleaveステートメントの存在を知りませんでした。

配列を抜けるときに使うのですね。