DS2ハッシュパッケージのdataset指定にSQL文書けるのは便利すぎるという話

DS2ではsetステートメントに　set { SQL文 };　っていう書き方が通るという話は何回もしてます。
（正確にはFEDSQLプロシジャで使用できるSQL文）

それだけでも結構すごいことだと思いますが、個人的に一番助かったというか、感激したのはハッシュテーブルのdataset指定の部分にSQL入れれるってことなんですね。待ってましたよこの機能！

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

data Q1;
ID=1;SEX='M';VAL=10;output;
ID=2;SEX='F';VAL=.;output;
ID=3;SEX='F';VAL=15;output;
ID=4;SEX='M';VAL=.;output;
run;

data Q2;
ID='A';SEX='M';VAL=10;output;
ID='B';SEX='M';VAL=14;output;
ID='C';SEX='M';VAL=13;output;
ID='D';SEX='M';VAL=15;output;
ID='E';SEX='F';VAL=9;output;
ID='F';SEX='F';VAL=8;output;
ID='G';SEX='F';VAL=11;output;
ID='H';SEX='F';VAL=16;output;
run;

最初のデータセットQ1についてVALが欠損の箇所がありますが、
ここを後のデータセットQ2で算出した性別ごとの平均値を使って補完したいとします。

正道で行くならDS2をmeansプロシジャでも何でもつかって性別と平均値のテーブル作って、
結合して処理でしょう。最低2ステップは必要になります。

1ステップでやるなら、ひとつはハッシュオブジェクトですが、詳細は割愛しますが結構面倒になります。
まあ、実はこの程度であればSQLで普通にかけるんですが、それも今回は置いておきましょう。

DS2プロシジャのハッシュパッケージだと、以下のように

proc ds2 libs=work;
data OUT1(overwrite=yes);
declare package hash h1();
declare double ID mean;
drop mean;
method init();
h1.dataset('{select SEX,mean(VAL) as mean from Q2 group by SEX}');
h1.keys([SEX]);
h1.data([mean]);
h1.definedone();
end;
method run();
set Q1;
h1.find();
if VAL=. then VAL=mean;
end;
enddata;
run;
quit;

余計なデータセットも作らずに1ステップで完了です。
ミソはh1.dataset('{select SEX,mean(VAL) as mean from Q2 group by SEX}');　の部分ですね
SQLの結果がそのままデータセットとしてみなされているわけですね。

以下は、まだ未解決の問題なんですが、これが例えばQ1の平均でQ1を補完するみたいな場合、
Q1を2重に開けなくて、つまりロック状態でエラーになるんですね。
データセットオプションにlocktable=shareっていうのが指定できるみたいなんですが、それをSQLで
指定しているQ1に適用するのってどうやるのかな？？
誰か知っていたら教えて下さいな。

それができればさらに便利なんだけど

DS2プロシジャでは、byステートメントを入れるだけで、自動でソートが発生する。ただ、細かいけど、そのソートはproc sortじゃなくてsqlのorder byという話

例えば以下のデータセットがあって

data Q1;
x=2;y=6;output;
x=1;y=1;output;
x=2;y=5;output;
x=2;y=7;output;
x=3;y=1;output;
x=1;y=2;output;
run;

以下のようにx yでsortして、first lastを利用して条件式をかくと

proc sort data=Q1 out=_Q1;
by x y;
run;
data A1;
set _Q1;
by x y;
if ^first.x and ^last.x then FL=1;
run;

といった処理ができます。

これをDS2でやる場合ですが、DS2では事前にデータセットをsortプロシジャにかけておかなくても
byステートメントに指定するだけで、勝手にsetに指定されているデータセットがsortされます。

つまり

proc ds2 libs=work;
data A2(overwrite=yes);
declare double FL;
method run();
set Q1;
by x y;
if ^first.x and ^last.x then FL=1;
end;
enddata;
run;
quit;

で同じ結果になります。

書きやすいですね～！ただ、コード上でproc sortがなくなったとはいえ、それで実行が早くなりはしません。結局、裏でsetするデータセットにソートかけてますから。

イメージ的にはbyをつけると、set Q1がset {select * from Q1 order by X,Y}に解釈されて
実行されるイメージですね。

で、勘のよい方は気づいているかもしれませんが、実はproc sortとproc sql のorder byのソートアルゴリズムは異なります。

それはSAS社のFAQでも紹介されています

http://www.sas.com/offices/asiapacific/japan/service/technical/faq/list/body/ba093.html

でDS2のソートも含めて、SAS社の例で3つのソートの結果を比較してみましょう

DATA test1 ;
     INPUT var1 var2 $ var3 $ ;
     DATALINES ;
     2 BBB  2_BBB_3
     1 BBB  1_BBB_1
     2 AAA  2_AAA_2
     2 AAA  2_AAA_1
     1 BBB  1_BBB_2
     2 BBB  2_BBB_4
     2 BBB  2_BBB_2
     2 BBB  2_BBB_1
     1 AAA  1_AAA_2
     1 AAA  1_AAA_1
     1 BBB  1_BBB_3
     ;
     RUN ;

     PROC SORT DATA=test1 OUT=test2;
       BY var1 var2 ;
     RUN ;
     title "sortプロシジャ";
     proc print data=test2 noobs;
     run;

     title "sqlプロシジャ order by";
     PROC SQL ;
       SELECT * FROM test1 
       ORDER BY var1,var2 ;
     QUIT ;
     
     title "DS2プロシジャ by";
proc ds2 libs=work;
data test3(overwrite=yes);
method run();
set test1;
by var1 var2;
end;
enddata;
run;
quit;
proc print data=test3 noobs;
run;

結果をみると

DS2でbyを指定した場合の自動ソートがSQLソートであることが確認できました。