文字列の部分一致による特殊な結合

今、以下のデータセットがあるとします。

data Q1;
X='ABCD';output;
X='BCBD';output;
X='ACAC';output;
X='ABAB';output;
X='ADCB';output;
run;

次に以下のデータセットをみます

data Q2;
Y='AB';Z=1;output;
Y='BC';Z=2;output;
Y='BD';Z=3;output;
Y='CA';Z=4;output;
run;

データセットQ1の1オブザベーション目のXは'ABCD'ですが、この文字列は
Q2のY='AB'とY='BC'を部分的に含んでいます。

このように片方のデータセットの文字列がもう一方に含まれている場合に結び付ける結合を考えてみます。

こういった結合になんか決まった名前あるんですかね？

SQLで、僕のレベルでぱっと思いつくのは、

proc sql noprint;
 create table A1 as 
  select X,Y,Z
  from Q1,Q2
  where X contains Y;
quit;

って感じです。
結果は

パフォーマンスは置いといて、まあとりあえず直積作ってから
一方が一方に含まれているって抽出式です。単純明快。

この方法の結果について、少し気になるのが

Q1のX='ABAB'に対してQ2のY='AB'を1オブザベーションで出力しているところです。
出現回数を考慮するのであれば、ABが2回出現しているので、2オブザベーション出力ということになります。

さて、出現回数分オブザベーションを起こすとなると、SQLでやる場合、かなり難易度があがりそうです（すみません、ノ―アイデアでした。分かる方、コメントお願いします）

まあ、SQLで難しいならデータステップでやりましょう。

data A2;
set Q1;
 do i=1 to Q2OBS;
  set Q2 nobs=Q2OBS point=i;
    do j=1 to count(X,Y);
     output;
end;
  end;
 drop j;
run;

で結果は

です。狙い通りABが2obs起きてますね

直積については
「詰めSAS4回目_直積（デカルト積、単純結合）を作成する」
http://sas-tumesas.blogspot.jp/2013/09/sas4.html


基本的に、なんかわけわからん条件がついた結合は、直積作ってからの条件式や、ループで、
だいたいケリがつきます。データが巨大になってくると、考え物ですが。

よくあるのが、文字列の類似度をスコア化して、一定水準以上の類似が見られる場合は
キーが一致したとみなして結合するような、キー一致条件緩和タイプのマージ処理です。
実践例としては、臨床開発ならフリーテキストで収集された薬剤名に薬剤辞書のコードをふる際の
候補レコードを出すような、まあ名寄せ処理的なケースですね。

基本、今回のコードに

「データステップ100満開　文字列がどれくらい類似しているかを定量化する」
http://sas-tumesas.blogspot.jp/2014/03/100.html

の内容を合わせてやれば簡単です。

以上です。


ちなみにもう9月ですが、ちょうどこのブログを始めたのが昨年の9月だったので
あっという間に1年です。
1-2カ月でネタ切れ閉店の予定が思いのほか長く続いてます

詰めSAS4回目_直積（デカルト積、単純結合）を作成する

直積（デカルト積、単純結合）はデータセット同士の全オブザベーションの組み合わせのようなもので3obsのデータセットと4obsのデータセットの直積をとると12obsになります。
実践で使用する機会がそれほど多いとは思いませんが、たまにあったり、また直積を作った方がスムーズに以後の処理ができることもあります。

問題は
data Q1;
input A B;
cards;
1 2
3 4
5 6
;
run;
【データセットQ1】




data Q2;
input C $ D $;
cards;
A B
C D
E F
;
run;
【データセットQ2】

の二つから

のようなデータセットを作ることです。

【解法1】

data A1;

set Q1;

 do i=1 to Q2OBS;

  set Q2 nobs=Q2OBS point=i;

  output;

 end;

run;

まずはデータステップで詰みです。nobs=オプションはデータセットの総obs数をとれるのですが

それをsetステートメントの前に使用できるというのは意外です。それをループ終点にして

point=でダイレクトアクセスする度に明示的にOutputすることで、Q1のSetによって、Q1が1obs読まれる度にi=1からQ2の最後のobsまでOutputされ、それがQ1のSetが全obs完了するまで行われるので結果として直積が作成されます。2つのデータセットのサイズによって変わりますが

基本的にはこれが最も効率の良い作成法だと思われます。コードもコンパクトにまとまってます。

【解法2】

proc sql noprint;

 create table A2 as

  select *

  from Q1,Q2;

quit;

直積はSQLの基本ともいえるので（単純結合というぐらいですし）、SQLで詰ませます。

cross　join等の方が綺麗な書き方なのかもしれませんが、とりあえず一番単純な書き方で実現です。SQLを使う場合の注意点は、ログにnoteとしてデカルト積が生成されたことを知らせるメッセージが必ずでるので、ログの内容に厳格に注意を払う仕事では留意する必要があります。

【解法3】

data A3;

 if _N_=0 then do;

  set Q1 Q2;

 end;

 if _N_=1 then do;

  declare hash hq(dataset:'Q2');

  declare hiter hi('hq');

   hq.definekey('C','D');

   hq.definedata('C','D');

   hq.definedone();

 end;

  set Q1;

  rc=hi.first();

   if rc=0 then output;

  do while(rc=0);

   rc=hi.next();

   if rc=0 then output;

  end;

 drop rc;

run;

ハッシュ反復子でやってみました。ハッシュはメモリにデータを展開するので、作成が高速化するか

と思ってやったのですが、コードが悪いのか、いくつかテストした条件ではデータステップやSQLの

1.5～2倍遅い結果しかだせていません。効率悪いはコードは長くて意味不明だわでいいとこなしな

感じですが、恐らくQ１がかなり巨大でQ2もそこそこの大きさのデータセットにして、オプションで

ハッシュの割当サイズを適度にすれば、速くなったりする気がします。いや、そもそも直積にハッシュはあまり意味がないのか。すみません、勉強中です。

詰めSAS1回目_全変数全obsで最大の値をとる

詰めSASの1回目のテーマは、データセット中の最大の値を1変数1obsのデータセットに格納する最善手を考えたいと思います。1手？詰めです。

まず、問いの内容は

data Q1;

input X Y Z;

cards;

1 4 3

2 9 8

7 6 5

;

run;

のデータセットから最大の値、この場合、Yの2obs目の9という値を

変数Mのみの新しいデータセットに格納するという目的です。

目的局面図は

です。

以下、解法です。

【解法１】

data A1;

 set Q1 end=eof;

 retain M;

 if max(X,Y,Z)>M then M=max(X,Y,Z);

 if eof;

 keep M;

run;

最大の値をだす場合に、第一感で思い浮かぶのはproc univariateやmeans等の

利用かもしれませんが、その場合は変数ごとの最大をだした後にデータステップで

その中からの最大をだすことになり2ステップになってしまうと思います。

なので、データステップ1回でやっつけたいのですが、SASのmax関数は横（行）方向の最大値

をとる関数なので、そこで出した値を次のobsに持ち込まないといけません。

なのでｒｅｔａｉｎを使って、持ち越し、endオプションで最終obsだけ残します。

【解法2】

proc sql noprint;

 create table A2 as

  select max(M) as M

   from 

  (select X as M from Q1

    union

   select Y as M from Q1

    union

   select Z as M from Q1)

;

quit;

SQL内でmax関数は１変数内を縦にみて最大値を返すので、じゃあ全部の変数を縦に

つないでからかけてやればいいんじゃないかと思って書いてみると

思ったより頭悪い感じのコードになりました。また、サブクエリを使うと、あまり一手といえなくなって

しまいますが。これならサブクエリ内でＸ、Y、Ｚで最大をとってからCASE文で比較して最大を

とる方がスマートだったかも。

【解法3】

data A3;

 obs1=1;

 obs2=2;

 obs3=3;

  set Q1(rename=(X=X1 Y=Y1 Z=Z1)) point=obs1;

  set Q1(rename=(X=X2 Y=Y2 Z=Z2)) point=obs2;

  set Q1(rename=(X=X3 Y=Y3 Z=Z3)) point=obs3;

   M=max(of X1--Z3);

  keep M;

  output;

 stop;

 run;

これは悪ふざけです。横に最大を返すなら、全部横にしてから、かければ

それで決着という方向で書きました。

1回目はここまで。


====================================================
後日、コメントをいただいて追記
====================================================
SQLでの記述について、突っ込みをいれていただきました。
以下のコードで詰みですね！スマート！！
思いつきもしませんでした！

標準SQLのmax関数は引数が1つなのですが、複数の引数を指定した場合
SASはSQLプロシジャ内でもSASのMAX関数と認識するそうです。
奥が深い、、

proc sql;
create table A4 as
select max(max(X,Y,Z)) as M
from Q1;
quit;