SQL:ALL述語と極値関数を用いた比較の挙動差異について

これも本を読みかえしていて、再発見した話です。

以下のように

data Q1;
do X=8,2,4,7,1;
 output;
end;
run;

data Q2;
do X=9,3,5,5,6;
 Y='A';
 output;
end;
run;

という二つのデータセットがあったとします。

そこで
Q2の全ての値より小さいQ1のオブザベーションを抽出せよ。という問題が出たとします。

その場合、

proc sql noprint;
create table A1 as
 select X
 from Q1
 where X<ALL(select X from Q2);
quit;

と書くことができます。

結果は

です。

ALLはこのように全ての値との比較を行うことができるキーワードです。

でも、恐らく多くの方がこう思われているはずです、最小値と比較すりゃええやん、と。

つまり

proc sql noprint;

create table A2 as

 select X

 from Q1

 where X<(select min(X) from Q2);

quit;

ということで、結果は先のコードと全く同じです。

さて、ではALLと極値関数比較に違いはないのでしょうか？

あるんです。

先ほどのQ1と、新しく以下のデータセットQ3に対して同じ処理を考えます

data Q3;

do X=.,3,5,5,6;

 Y='A';

 output;

end;

run;

に対して

proc sql noprint;

create table A3 as

 select X

 from Q1

 where X<ALL(select X from Q3);

create table A4 as

 select X

 from Q1

 where X<(select min(X) from Q3);

quit;

とすると

ALLを使って作成したデータセットA3は

minを使って作成したA4は

です。

つまりALLでは、nullが入ったサブクエリ結果をとると、正しくできないので注意ということです。

whereでnullを省けば、作成可能です。

続いて、以下のコードではどうでしょうか

proc sql noprint;

create table A5 as

 select X

 from Q1

 where X<ALL(select X from Q3 where Y='B');

create table A6 as

 select X

 from Q1

 where X<(select min(X) from Q3 where Y='B');

quit;

Q3に対してwhere Y='B'としていますが、Q3のYの値は全て'A'なので、このサブクエリは空集合です。

さてこのように空集合が引数となった場合のALLの結果は

つまり、条件がかからずQ1がそのまま抽出されます。

対してmin関数で作成したA6については

です。

つまりALLと極値関数比較の違いは

★サブクエリ結果にnullが含まれる場合

　　ALL→×

　　極値→○

★サブクエリ結果そのものが空集合である場合

　　ALL→全オブザベーションがそのままでる

　　極値→×

といった感じなので、そこを踏まえて使いましょうということでした。

詰めSAS6回目_由来するデータセットの情報で処理をする

以下の２つのデータセットを、変数名を共通のものにリネームしつつ縦結合し、なおかつ結合後のデータセットで、もともとどちらのデータセットにあったオブザベーションか判別できるように情報を付与せよといった問題になります。

data Q_1;
 A=1;B=2;output;
 A=3;B=4;output;
run;

data Q_2;
 C=5;D=6;output;
 C=7;D=8;output;
run;

上記の2つのデータセットから、下のデータセットを作ります。

なお、1ステップで行うのが最短で、新規データセットを作成する問題なので
appendプロシジャの採用は今回はおそらく悪手です。


【解法1】
data A_1;
 set Q_1(in=in1 rename=(A=X B=Y))
      Q_2(in=in2 rename=(C=X D=Y))
;
 if in1 then BASE='Q_1';
 if in2 then BASE='Q_2';
run;

inデータセットオプションはmergeステートメントと使って、外部結合（片方のオブザベーションを全て残す）ような処理の際によく使われますが、本来、そういった限定的な使用法ではなく、データセットの帰属にフラグをたてれる優秀なオプションなのでより柔軟に使用されるべきだと思います。


【解法2】
proc sql noprint;
 create table A_2 as
  select A as X,B as Y,'Q_1' as BASE
   from Q_1
    union all
  select C,D,'Q_2' as BASE
   from Q_2;
quit;

この方法で面白いのは、変数名をX,Yに変える処理をQ_1に対してしか行っていないのに後段の
Q_2の変数C、DもX,Yになって縦結合される点です。
これは unionにcorrをつけていないことに起因します。corrをつけると縦結合する際に変数名が同じ物同士をつないでくれますが、つけれなれば、どんな変数名であっても１つ目の変数は1つめの変数と結合されます。定義情報は一番最初のテーブルのものが強制されます。通常はそんなおっかない性質は扱いづらいので　unionにはallとcorrをつけます。allを抜くと勝手に重複データを消してきます。
SASのSetステートメントの挙動と同じなのはunion corrで繋いだ場合ということになります。




=================================
おまけ　in=は優秀
=================================
たとえば以下のようなデータセットがあったとします。

data GROUPDATA;
 input GROUP $10. SEX $;
 cards;
グループA 男性
グループA 男性
グループA 男性
グループA 女性
グループA 男性
グループA 女性
グループA 男性
グループA 男性
グループA 男性
グループA 女性
グループA 男性
グループB 男性
グループB 女性
グループB 女性
グループB 男性
グループB 女性
グループB 男性
グループB 女性
グループB 男性
グループB 女性
;
run;

ここでAグループ、Bグループ、全体で見たときの男女の人数をカウントせよという問題があった
とします。

そういった時setステートメントで同じデータセットを縦につないで
一旦オブザベーション数が2倍になってデータが重複したデータセットをつくるコードを書きます。
ただし2つめのデータセットにデータセットオプションでin=を指定し、後にifでin=でたてた変数が
1の場合にグループ情報をダミー変数に置き換えます。
あとはグループ集計すればいいのです。
言葉で説明しにくいので実際にやってみてください、SASっぽいなぁと思わせる処理です。

data GROUPDATA_1;
 set GROUPDATA
      GROUPDATA(in=in1);
   if in1=1 then GROUP='全体';
 run;

proc freq data=GROUPDATA_1 noprint;
  tables GROUP*SEX/out=OUTPUT(drop=percent);
run;