いただいたコメントの回答例_インデックスをbyで指定することで事前のproc sortを回避する

前回の投稿にコメントでご質問いただきました。本当に有難うございます。コメントをいただけると元気がでます。

少し回答が長くなりそうだったので、ここでお答えします。

まずいただいたコメントから

=================================================================================

匿名2014年1月22日 23:36

いつもブログを読んで勉強させてもらってます。現在自分が所属する部署ではSAS上でsort　summary を行うことが多いため、そのための簡単なマクロを設定したいと思っています。sortではなくindexを設定する方が早いのでindexを活用していますが、index設定の際のキーが複数の場合と一つの場合の両方で使えるマクロは作成するのによいアイデアはありませんか？今の自分の知識では以下のマクロが精一杯です。

%macro sum(a,b,c,x,y);

/*

a ソートキー

bサムキー

c　ソート項目が単数なら1

x　インプットデータ

y　アウトプットデータ

*/

%if &c=1 %then %do;

proc datasets nolist;

modify &x;

index delete &a;

index create &a;

run;

%end;

%if &c^=1 %then %do;

proc datasets nolist;

modify &x;

index delete keyZZZ;

index create keyZZZ(&a);

run;

%end;

proc summary data=&x noprint;

var &b;by &a;

output out =&y

sum=&b ;

run;

%mend sum;

=================================================================================

なるほど、僕はマクロはあまり上手ではないですが、とりあえず、cのパラメータ引数が少し無駄手な感じですね。

まず、サンプルデータセットを勝手に作ります

data Q1;

X='い';Y='A';Z=2;output;

X='い';Y='B';Z=3;output;

X='ろ';Y='A';Z=4;output;

X='い';Y='B';Z=1;output;

X='は';Y='A';Z=2;output;

X='ろ';Y='B';Z=5;output;

X='い';Y='A';Z=1;output;

X='は';Y='A';Z=2;output;

X='い';Y='B';Z=2;output;

run;

で、わかりやすい感じにするためやや冗長かもですが、以下の感じでしょうか？？

とりあえず、引数1個減らしました。

options msglevel=i;

%macro sum2(a,b,x,y);

/*a:ソートキー bサムキー xインプットデータ yアウトプットデータ*/

%let c=%index(&a,%str( ));

proc datasets nolist;

modify &x;

 index delete _all_;

 %if &c=0 %then %do;

  index create &a;

 %end;

 %if &c^=0 %then %do;

  index create keyZZZ=(&a);

 %end;

run;

proc summary data=&x;

 var &b;

 by &a;

 output out =&y

 sum=&b ;

run;

%mend sum2;

cで手動で1フラグをたてて、判定していた部分を、マクロ引数に半角スペースが含まれているか

どうかで判定させてみました。入っていれば複数変数が指定されているはず、と考えました。

どのインデックスがbｙステートメントで採択されたかを明示するためoptions　でmsglevelをiにしています。

元コードにあったdeleteは、元々設定されているインデックスを一度リセットする意図だと思うので

いっそ　_all_で全殺ししました。

/*単一インデックスの実行例*/

%sum2(X,Z,Q1,A1)

/*複合インデックスの実行例*/

%sum2(X Y,Z,Q1,A1)

で、ふと思ったのが、ちょっと巨大なデータセット相手の経験が豊富ではないのでインデックスのパフォーマンスでの恩恵度合いが推定できないのですが、ソートかますと時間がかかり、それを飛ばすためだけにインデックスを使用してるということで、

summaryプロシジャでデータセットを作成するだけであれば

%macro sum3(a,b,x,y);

proc summary data=&x nway;

 var &b;

 class &a;

 output out =&y

 sum=&b ;

run;

%mend sum3;

/*実行例*/

%sum3(X Y,Z,Q1,A2)

class　ステートメントで今までbyで指定していた変数を指定してやれば、事前のソートも必要なく、単数でも複数でも可能で結果は同じになります。感覚的に、結構大きなソートされていないデータセットでもそこそこ早いような気がしてますが、どうなんでしょうか。

もしかしたら、的外れな回答、或いは既に知っていることを偉そうに能書いただけかもですが、その場合は申し訳ございません。

何かご質問ございましたら、どなたでもいつでもご連絡ください。

sasyupi@gmail.com

データステップでインデックスを利用して結合を行う。Setステートメントとkey=オプション、そして自動変数_IORC_を使って

SASでインデックスを使うなら、やっぱりデータステップで活かしてみたいですよね。

でkey=データセットオプションと_IORC_の話なのですが、とっつきにくそうに見えて、実は単純な原理なので、マニアックな話だと思わずに、見てみてください。

今、以下の2つのデータセットがあったとします。

data Q1;

do X=1 to 10;

 do Y='A','B','C';

  output;

 end;

end;

run;

data Q2(index=(XY=(X Y)));

 X=1;Y='C';Z=100;output;

 X=6;Y='A';Z=200;output;

 X=8;Y='B';Z=300;output;

 X=9;Y='D';Z=400;output;

run;

で、結論からいうとSEＴ データセット名　key=結合に使うインデックス名;

で、事前にソートされていないデータセットについて結合を行うことができます。

（ソートが必要ないのは、順番にobsを読み込むのではなくインデックスを見てダイレクトアクセス

するためです）

data A2;

 set Q1;

 set Q2 key=XY;

run;

として実行します。

すると、結果は

となります。

よく見ると、マッチングできたオブザベーションの値が、マッチできてないobsに

覆いかぶさるように引き延ばされているのがわかると思います。

通常、こんな結果は欲しくないはずです。

さらにログがえらいことになります。

ようするに、値がとってこれないということがエラーになっているのですね。

で、_IORC_とかって文字がでていますが、これは何だということです。

正体を現せということで、以下のコードを実行します。

data A3;

 set Q1;

 set Q2 key=XY;

 A=_IORC_;

 E=_ERROR_;

run;

あいかわらずログは変わらずですが、データセットの中身をみてみると

とりあえず、わかることは_IORC_は結合がうまくいった箇所では0で、そうでない場所では

わけわからん数字になっていて、_ERROR_もたつということです。

ちなみに_ERROR_が1になるということはログにERROR：がでるということです。

実はIORCとはInput Output Return Codeの略で、インデックスによるキーマッチングを

行った際のエラーコードを返してくれるのです。そのエラーの種類によっていろんな数字になる

そうで、とりあえず、今回を見る限り、ルックアップデータセットに対応するインデックスが

ない場合は1230015というエラーコードになるようですね。

成功すれば必ず0になるわけです。

なるほど、ここまでわかればもう充分！要は_IORC_が0かそれ以外かで、ちょちょいと分岐すれば

思い通りにできるということです。

まずは、キーが一致したオブザベーションのみを残す、いわいる内部結合の表現

data A4;

 set Q1;

 set Q2 key=XY;

 if _IORC_=0 then output;

 else _ERROR_=0;

run;

なんて、シンプル！0なら出力して違うなら、しない、違う場合は_ERROR_=1のままにしとくと

ログがうざいので_ERROR_を0で封じるということです。

結果は

ログも

綺麗。

じゃあ、次は外部結合です。Q1のobsを全部残しつつ、キーが一致するQ2の値をつなげる処理。

data A5;

 set Q1;

 set Q2 key=XY;

 if _IORC_^=0 then do;

  Z=.;

  _ERROR_=0;

 end;

run;

もうわかりますね、引き延ばしを抑制しているわけです。

今回はとてもシンプルな例のみ説明しましたが、

_IORC_をうまく使うと色々もっとできます。

僕も勉強中なので、より極めたい方は海外の論文をあたれば、山ほどでてきます。

SQLの結合もインデックスを参照することで効率化

SQLを先に修められた方からすると、当たり前なのかもしれませんが、、

options msglevel=i;
data Q1(index=(XY=(X Y)));
do X=1 to 10;
 do Y='A','B','C';
  output;
 end;
end;
run;

data Q2(index=(XY=(X Y)));
 X=1;Y='C';Z=100;output;
 X=6;Y='A';Z=200;output;
 X=8;Y='B';Z=300;output;
 X=9;Y='D';Z=400;output;
run;

proc sql noprint;
 create table A1 as
 select Q1.X,Q1.Y
 from Q1 inner join Q2 on Q1.X=Q2.X and Q1.Y=Q2.Y
;
quit;

を実行するとログは

となり、結合にインデックスが利用されていることがわかります。
joinも、内部的には直積からの抽出なのでwhere句と同じ扱いということですよね？？

インデックスへの参照が無効になるwhereステートメントの書き方を把握して、なるたけ回避する

インデックスはつけてされいれば、いつでもSASがそれを使って高速に処理してくれる
というものではありません。

インデックスの参照が無効化されるケースがあるので
回避できる部分はプログラマが気をつけなければいけません。


とはいえ、SASの場合は、実のところ他のRDBMSに比べて、結構強力に参照してくれます。
たとえば変数Xに単一インデックスXがついていた場合

where X ^=1;
や
where X not in (1,2);
where X is null;
where substr(X,1,1)='A';

などは他のシステムでは定番で強制的にインデックス非参照の場合が多いのですが
SASはそうではありません。

で、詳しくはSAS9.3言語リファレンス　解説編をSASのホームページの日本語版SASオンラインヘルプから落としてきて、26章　SASデータファイルのSASインデックスについてのWHERE式処理に対するインデックスの使用の項あたりを読んでください。

以上。

だと、あまりにそっけないので大原則だけ実践で説明します。
まず、インデックスが使用されたかどうかを判別できるoptions msglevel=i;をしてから
以下のデータセットを作成します。

options msglevel=i;

data Q1(index=(X YZ=(Y Z)));
do X=1 to 10;
 Y=X**2;
 Z=X**3;
 output;
end;
run; 

変数Xでもって単一インデックスXをつくり
変数YとZのペアで複合インデックスYZをつくっています。

では早速

data OK;
 set Q1;
 where X<5;
run;

この場合インデックスXが参照されます。


では、次に

data NG;
 set Q1;
 where X-5<0;
run;

を実行すると

インデックスXが参照されることは決してありません。
式変形しただけで、抽出の意味は同じなのに
インデックスが利用される場合とされない場合があるというのは
こういうことです。

で、原則としては
whereステートメントの左辺は裸にするということです。
余計な四則演算や関数をつけているとインデックスは利用されないので
全部どうにかして右辺につっこみましょう。
※ただし、TRIM関数とSUBSTR関数は例外的に使用できます。

次は少し応用編なのですが、

data OK;
 set Q1;
 where Y=1 and  Z=1;
run;

を実行すると、YとZにインデックスYZをはっているので、参照されます。

しかし、

data OK;
 set Q1;
 where Y=1;
run;

のように変数Yだけで実行するとどうなるでしょうか？

なんとYZが参照されるのです。
このように複合インデックスにおいても、インデックスの一部要素である変数を
抽出式に利用すると、部分的に利用してくれるわけです。

じゃあ同じ理屈で
data NG;
 set Q1;
 where Z=1;
run;

のようにZだけで実行すると、

次は、なんとインデックスが参照されません。
これは、実はインデックスを作成する際に(index=(X YZ=(Y Z)))と
Y Zの順番に宣言しているからなのです。
つまり、複合インデックスは、部分的に内包される変数で利用できるが
宣言順通りにならない場合は、無効化されるということです。

なので、例えば症例番号とビジットで一意になるデータがあった際に
複合インデックスを（症例番号　ビジット）で作るのか（ビジット　症例番号）でつくるのかは
その後、個別に症例を抽出するような処理の発生が考えられるか、あるいはビジットごとに
全症例を抽出するような処理が発生するか、どちらであるかを考慮して作成するといいですねと
いった話でした。

なお複合最適化についても詳しくはリファレンスを参照してください。

インデックスが利用されたかどうかを明示する options msglevel=i

例えば、以下のコードで3000000obsのデータセットを作り
全く同じ内容の変数ZとZ2を作成しZの方にだけインデックスをつけます。

data Q1(index=(Z));
 do X=1 to 1000000;
  do Y='A','B','C';
   Z=cats(put(X,best.),Y);
   Z2=cats(put(X,best.),Y);
    output;
  end;
 end;
run;

まずインデックスを参照せずに抽出をしてみます。

data A1;

 set Q1;

 where Z2='10B';

run;

となりました。環境によって処理時間はかわりますので参考まで

処理時間10.38 

CPU時間1.48

で次にインデックスが掛った変数で抽出します

data A1;

 set Q1;

 where Z='10B';

run;

処理時間0.03

CPU時間0.01

となります。

歴然と速くなっています。

これは、インデックスを参照して抽出をしているからです。

ただ、まあ、速くなったのはわかるんですが、、、

なんか、インデックスが使われたかどうか何もわからないと

どうもモチベーションがあがりません。

あ、ちなみにSASは、データセットの色々な要素から、インデックスを使った方が速くなる

と判断すれば利用し、いや、かえって全部順番に読んだ方が速いと判断したら

インデックスがついていても使いません。

だいたいこういう時につかわれるといったセオリーはあるのですが基本的にはブラックボックス

SASの心の赴くままにです。

せっかく親切にインデックスつけたのに、それじゃあ、つれないですよね。

なのでここで久々の

options msglevel=i;

です。

これをつけるとログが、やたら細かい情報も教えてくれるようになり、

マージで変数の上書きに注意するときなどに重宝すると紹介したものです。

で、これをかけてから

同じように実行すると

となって、INFOで、ちゃんと教えてくれます。

色をつけたい方は拡張エディタの設定をいじってください。

さて、今後はどういった処理だとインデックスが有効かを説明します。

実はwhere文の書き方によって、結果は同じ意味でも

インデックスを絶対使ってくれない損な書き方があります。

それを知っていないと、せっかくインデックス作っても無駄骨です。

また、今回は抽出の処理時間をみて

インデックスすげーといった紹介をしましたが、

何事にも裏の面があります。

実はデータセットが巨大になればなるほど、インデックスを付与することに

パワーが必要になって、作成に時間がかかったりして、トータルで考えると速くなっていない

ケースもありえます。

その辺りのトレードオフは、常にケースバイケースなので慣れが必要だと思います。

巨大なデータセットに対して、後で何度も、抽出等の処理をかけなければならない場合は

作成時に付与しておくと、以降の処理で節約できた時間の合計が、作成に余計にかかる時間を

こえて、やったねとなったります。

例えば医薬系でいうと、MedDRAや薬剤コード辞書などを永久データセットにする際にインデックスをつけておけば後に、CRFデータとそれらをマッチングしたり、検索をかけたりする際にとても
役立ちます。

SASにおけるインデックスの話_まずはindexを作成する

SASデータセットにつける索引機能　インデックスの話です。index関数の話ではありません。
SASの処理速度が高速なことと、SASでは、データセットを新規に作成して処理を進めていくことが多いので、新規データセットを普通に作ると消えてしまうインデックス（modifyを使えば消さずに更新できる）は、それほど使われていない気がします。

僕もDB畑でのプログラム経験はなく、実行時に、使用されるかされないかわからない曖昧なインデックスについては、なんか、どう使っていいのかわからないものとしか思っていませんでした。

ただ、プログラムの幅を広げようとインデックスにも手をだしてみたところ、なかなか面白くて、また世界が少し広くなりました。
何が面白いかというと、SQLやハッシュオブジェクト、あるいはpoint=オプションの面白さにも通じますが、SASの1オブザベーション目から最終まで順番にという基本的な方針、シーケンシャルアクセスに逆らうことができるところでしょうか？1つ1つ値を読んで判断するのではなく、インデックスをみて、ダイレクトにそのobsを処理するみたいな。

まだ初歩的なことしかできませんが、少しずつ紹介します。
間違ったことを書いてしまったらご指摘ください。

まずはインデックスの作り方から。

適当なデータセットを作ります。

data Q1;
 do X=1 to 10;
  do Y='A','B','C';
   Z=cats(put(X,best.),Y);
    output;
  end;
 end;
run;

これから、このデータセットで

変数Zに対して単一インデックス「Ｚ」を作成し

変数XとYを1組として複合インデックス「XＹ」を作る方法を3パターン紹介します。

単一インデックス「Ｚ」にはインデックスが一意でなければいけないという制約と

欠損値であってはいけないという制約をつけます。

なお、単一インデックスはかならず、変数名と同じ名前のインデックスになります。

複合は自分で名前をつけられます。

では

【datasetsプロシジャを使う方法】

proc datasets nolist;

 modify Q1;

 index create Z/unique nomiss;

 index create XY=(X Y);

run;

quit;

を実行すると

となり、データセットのプロパティでインデックスのタブをみると

となっています。

ちなみに

proc datasets nolist;

 modify Q1;

 index delete Z XY;

run;

quit;

で消せます。

【SQLプロシジャを使う方法】

データベース操作に慣れた方であればSQLでかかれることが多いと思います

proc sql;

 create unique index Z on Q1;

 create index XY on Q1(X,Y);

quit;

で作れます。

要注意なのは、SQLプロシジャでは非欠損値制約 nomissが使えないことです（多分）

proc sql;

 drop index Z,XY from Q1;

quit;

で消せます。

【データセットオプションで作成】

data A1(index=(Z/nomiss/unique XY=(X Y)));

 set Q1;

run;

この方法はお勧めです。データセット作るついでにつけることができるので。