掲示板質問の回答例：現在使用中のFootnoteの最終番号を取得する方法

掲示板の方に、質問をいただきました。有難うございます！掲示板に返事書こうと思ったのですが、結構やってて面白い内容だったので記事にしました。

（掲示板：http://tumesas.progoo.com/bbs/tumesas_topic_pr_6.html）

ただ、本当にこの方法でいいのかわからないので、何かアイデアがあればコメントでも掲示板にでもメッセージください。

さて本題。質問は

なるほど、、。そういえば考えたことない、、。

検索しても、それっぽいのが出てこないので、自分の頭で考えてみました。

以下になります。

まず

data Q1;

X=1;

run;

title 'タイトル1';

footnote 'フットノート1';

footnote2 'フットノート2';

footnote3 'フットノート3';

proc print;

run;

を実行すると、当然

こうなるわけですね。

さて次のプロシジャ実行時に、すでにfootnote3まで使ったことを取得し、footnote4が展開されるようにしたいわけですね。仮にfootnote6まで使っていればfootnote7みたいな感じです。

さて、定義したタイトルやフットノートの情報はどこにいるでしょうか？

SASHELPライブラリの中には、SAS社が用意したサンプルデータセットと共に、SASの様々な定義情報がリアルタイムで更新されるビューが用意されてます。

（ちなみにSASHELPのサンプルデータセットを間違えて全部消したおバカさん（僕です）はSAS社のWebページから再ダウンロードできます）

この中のVTITLEを開けてみると

発見！

typeのTはタイトル、Fはフットノートです。

後は、もう簡単ですね

proc sql noprint;

 select max(number) into:fno

 from dictionary.TITLES

 where type='F' and text^='';

quit;

です。

sashelp.VTITLEはSQLの中ではdictionary.TITLESと書けます。そういうものなんです。

これでマクロ変数fnoには3が入ります。

そして以下

footnote%eval(&fno+1) 'プログラマー名:SASYAMA';

proc print;

run;

を実行すると

となって、確かに最後にプログラマー名がでてきます。

こんな感じです。

まあ、改善点としては、定義可能最大値が10なので、footnote10が既に定義されている場合や、全く定義されていない状態で実行した場合エラーにならないような分岐が必要ですが、そこまではやってません。

SQLのサブクエリ（副問い合わせ）は習うより慣れろ？どこにでも書けるから取り敢えず書いてみようの話

サブクエリはSQL文の中にさらに括弧に入ったSQL文がいるよってやつです。
そのサブクエリの使い方がよくわかりませんと言われます。

話を聞いていると、特に相関サブクエリ、つまり主クエリとサブクエリに関係があって、主クエリの結果に対してサブの結果が連動して作用するようなやつが特に苦手な人多いなぁと思います。

SQL、特にそういう複雑なものについては、一通り理屈を本などで勉強したら、あとはもう、たくさん書いて、人が書いたものもたくさん読んで、理屈で理解するよりかは、イメージを体染みにつけるのが結局近道だと思います。

まず、SQLって何？って方はSAS忘備録のSQL入門を1から順番に読んでいくことをお勧めします。
http://sas-boubi.blogspot.jp/2014/04/sql1select.html


でサブクエリですが、よく、SELECT句、WHERE句、FROM句、どこに書いたらいいかわからないと質問されますが、乱暴にいっちゃうと、取り敢えず、どこに書いても何とかなるよ！です。

例えば

data Q1;
GRPNAME='A';ID='001';VAL=100;output;
GRPNAME='A';ID='002';VAL=90;output;
GRPNAME='A';ID='003';VAL=120;output;
GRPNAME='A';ID='004';VAL=80;output;
GRPNAME='A';ID='005';VAL=150;output;
GRPNAME='B';ID='006';VAL=70;output;
GRPNAME='B';ID='007';VAL=60;output;
GRPNAME='B';ID='008';VAL=180;output;
GRPNAME='B';ID='009';VAL=110;output;
GRPNAME='B';ID='010';VAL=190;output;
GRPNAME='C';ID='011';VAL=60;output;
GRPNAME='C';ID='012';VAL=120;output;
GRPNAME='C';ID='013';VAL=130;output;
GRPNAME='C';ID='014';VAL=50;output;
GRPNAME='C';ID='015';VAL=200;output;
run;

上記のようなデータがあるとします。A、B、Cの3つのグループに5人ずつ人がいて、VALという何らかのスコアを持っているとします。

上記のデータから、自分の所属するグループの平均スコアより、高いスコアを持っている人を抽出しなさいという問題を考えます。

まず基本的な考え方として、グループごとの平均をだすSQLは

proc sql;
 select GRPNAME,avg(VAL) as AVGVAL
 from Q1
 group by GRPNAME;
quit; 

な感じで、結果は

これをサブクエリとしてどう使うかですが、

まずはWHERE句に突っ込むパターン

proc sql noprint;
 create table A1 as
  select GRPNAME,ID,VAL
  from Q1 as A
  where VAL>(select avg(VAL)
             from Q1 as B
             group by GRPNAME
             having A.GRPNAME=B.GRPNAME);
quit;

で結果は

同じテーブルをあたかも違うテーブルとみなす自己参照はQ1　as A やQ1 as Bのように仮名(alias)を付与します。ちなみにasは省略可能ですが、慣れないうちは逐一つけた方がいいです。
having A.GRPNAME=B.GRPNAMEがポイントで、これによって、主クエリののGROUPNAMEの値と同じ値をもったサブクエリの集約結果が条件式にはまります。


つづいてSELECT句に突っ込むパターン

proc sql noprint;
 create table A2 as
  select GRPNAME,ID,VAL
         ,(select avg(VAL)
           from Q1 as B
           group by GRPNAME
           having A.GRPNAME=B.GRPNAME) as AVGVAL
  from Q1 as A
  where VAL>calculated AVGVAL;
quit;

で結果は

抽出されているIDを見ると先ほどの結果と同じであることがわかります。
基本的に考え方は先ほどの例と同じですが、ポイントはwhere VAL>calculated AVGVAL;の
calculatedの部分ですね。
calculatedの説明はSAS忘備録の「SQLの小技CALCULATEDキーワード」http://sas-boubi.blogspot.jp/2013/12/sqlcalculated.htmlをみていただければわかりますが、基本select句で新規に定義した変数でwher句の条件はかけないのですが、calculatedをつけるとOKなわけです。


さて最後にFROM句に突っ込む場合

proc sql noprint;
 create table A3 as
  select A.GRPNAME,ID,VAL
    from Q1 as A 
    left outer join
    (select GRPNAME,avg(val) as AVGVAL
        from Q1
        group by GRPNAME) as B
     on A.GRPNAME=B.GRPNAME
  where A.VAL>B.AVGVAL;
quit;

結果は

と、やはり同じです。

これは特に説明の必要ないですね。集計した結果と元の結果を結合して、それをFROMで指定して、抽出してるだけです。


さて、このように、どこに書いても、同じ結果を導くことができました。
あまり悩まずにどんどん書いて、どんどん失敗もして、勉強していきましょう。

ちなみにの話ですが、サブクエリは、処理速度の面からいうと最善ではない場合も多いので
少し留意しておきましょう。

縦に欠測値のカウントの話

ひとつ前の記事に書きましたが、SAS社様の公式TwitterやFacebookでこのブログを紹介していただきましたので、ちょっとお行儀よく、久しぶりに基本的かつ真面目な話題をします。

各変数ごとに欠測の値が何オブザベーションあるかを1ステップでカウントしたいとします。

要は

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

上記みたいなデータセットから

を作りたいということです。


さて、ぱっと思い浮かびました？意外と難しかったりしませんか？

proc meansが思いついた方もいたと思いますが

proc means data=Q1 noprint;
 var X Z;
 output out=A0(drop=_TYPE_ _FREQ_) nmiss=XMISS ZMISS;
run; 

のように数値変数X Zに対してはnmiss=で欠損値カウントが可能ですが、文字値のYはvarに指定した時点でエラーになってしまいます。

freqが思いついた方もいるかもしれませんが、1ステップで、上記の形のデータセットを作るのは難しいはずです、多分。

基本、データを縦に考える時はSQL使えっていうのは僕の中の原則なので、

proc sql noprint;
 create table A1 as
   select sum(missing(X)) as XMISS
          ,sum(missing(Y)) as YMISS
          ,sum(missing(Z)) as ZMISS
       from Q1;
quit;

は、正解です。上記の回答データセットの結果になります。
missingは欠損値の場合1、非欠損値の場合0なので、それをsumで合計すればよいわけです。
わざわざcase文書かなくても、SASのmissing関数をSQLにぶち込めちゃうところがグレートですね！

次にこれぞSASのデータステップ！!って感じで書くなら

data A2;
 set Q1 end=eof;
 retain XMISS YMISS ZMISS 0;
  if missing(X) then XMISS+1;
  if missing(Y) then YMISS+1;
  if missing(Z) then ZMISS+1;
  if eof;
 drop X Y Z;
run;

で、結果は同じです。1行ずつ順番に読んでいくSASの正道って感じですね。

やっぱ上記のようなステップ書いてる時は、なんか居飛車、それも矢倉とか指してる感覚に似てますよね。逆にSQL書いてる時は、角交換系の振り飛車の間隔ですね。ハッシュオブジェクトもそれに近いけど、もしかしたら横歩取りの感覚かも。
僕はもちろん振り飛車党です。
結局脱線してるっていう

proc SQLの _methodや_treeオプションで内部でどのような処理が行われているかを把握する。読み解くのにかなり知識がいるけどデバックに使えます

以前、「SQL optimizerで実行アルゴリズムを強制する」
http://sas-tumesas.blogspot.jp/2014/04/sql-optimizer.html

でSQL実行の際の内部処理の話をしましたが、今回もそれに関連する話です。
ただ、先に言い訳ですが、僕はまだSQLの内部処理のような深い部分は勉強中で大して理解できていないので、機能の紹介だけです。

data Q1;
X='い';A=1;output;
X='ろ';A=2;output;
X='は';A=3;output;
run;

data Q2;
Y='い';B=1;output;
Y='ろ';B=2;output;
Y='は';B=3;output;
run;

という2つのデータセットがあって、Xの値とYの値で内部結合する処理を考えた場合、

proc sql _method;
 select X
       ,A
       ,B
       ,A+B as AB
 from Q1 inner join Q2
 on X=Y
;
quit;

と _methodオプションを追加すると、ログに

とでてきます。

sqxslctはseleｔの実行、sqxsrcは行が読み込まれたことを示してます。
sqljhshはハッシュ法による内部処理が実行されたことを示しています。
magicオプションの値を変えると、ここの記述が変わることが確認できます。

詳しくは、この読み方に言及した論文がいくつか挙がっているのでそちらを参照ください。
（例えばhttp://support.sas.com/resources/papers/proceedings13/200-2013.pdf）


次に

proc sql _tree;
 select X
       ,A
       ,B
       ,A+B as AB
 from Q1 inner join Q2
 on X=Y
;
quit;

と_treeオプションを追加すると、ログに

こういうツリープロットが出現します。

うぉ～、なんじゃこりゃって感じですが、これが、SQLで実際どのように構文を解釈して、実行するかの処理を示しています。

以下はちょっと怪しい解釈

2階層目にJOINってでてますよね、結合処理であることを示してます。
3階層目の/-OBJのところが、最終的な出力部分の定義です。
んで/-FROMはその処理のために読み込まれるテーブルです。
/-CEQのところはXの値とYの値の同値判定していることを示しています。
/-ERLYのところは今回、AとBの値を足してABという新規変数を作っているのですが、そこの部分です。

詳しくは、これまたたくさん論文がでてるので、それで理解するようにしていただけたらと思います。

（例えばhttp://www2.sas.com/proceedings/sugi30/101-30.pdf）


SQLって、結構コードから結果を頭の中で想像するのが難しいですよね。
複雑で長いものになると、なにやってんだこれ？ってなります。
そういった際にこういったオプションをつけると、読み解く助けになるかもしれません。

変数ごとの転置結果を再マージするような処理について、変数がいくつあろうが必ず2ステップでケリをつける方法。do until(last.変数)ループを使って

例えば、以下のデータセット

data Q1;
X='い';Y=1;Z='A';W=1;output;
X='い';Y=2;Z='B';W=1;output;
X='い';Y=3;Z='C';W=2;output;
X='は';Y=4;Z='D';W=2;output;
X='は';Y=5;Z='E';W=.;output;
X='ろ';Y=6;Z='E';W=3;output;
run;
proc sort data=Q1;
 by X;
run;

を使って

のようにXを起点にしてその他の変数を全て転置したデータセットを作成せよと言われたらどうしますか？

多分、すぐに思いつくのが

proc transpose data=Q1 out=_Y(keep=X Y_:) prefix=Y_;
 var Y;
 by X;
run;
proc transpose data=Q1 out=_Z(keep=X Z_:) prefix=Z_;
 var Z;
 by X;
run;
proc transpose data=Q1 out=_W(keep=X W_:) prefix=W_;
 var W;
 by X;
run;

data A0;
 merge _Y _Z _W;
 by X;
run;

だと思います。

ただし、この方法だと、転置する変数分だけtransposeを書くため、どうしても処理に無駄があるように僕はずっと感じてきました。

特に以前、仕事で、臨床検査値と臨床検査基準値を、検査日と基準値適応開始日を比較しながらマージすることが多かったため、その過程で上記のコードをよく書いていたため、もっと改良できないかと常に思っていました。

それに対する一つの答えが、今年ユーザー総会で発表したハッシュオブジェクトに関する論文で、ハッシュオブジェクトを利用することで、そもそも上記のようなデータセット加工を行わずに、ダイレクトにマッチングができるという内容でした。

それで少しは気が晴れたのですが、それはあくまで、連続transposeと再マージ処理を避けるテクニックなので、まだひっかかりがありました。

そこで今回、2ステップにはなりましたがdo untilの終了条件にlast.変数を使う特殊なループ法でやってみました。

ちなみにこのループ法、海外ではDow loopと呼ばれていて、「SAS dow loop」で検索すると、その応用法がたくさんでてきます。

Dowの「w」の文字はIan Whitlockという方の名前からつけられました。2000年にこの方が、初めてSAS-Lというコミュニティで、この方法を発表し、それがあまりに画期的であったため、リスペクトを込めて、ループの終了条件にfirstやlast等を使用するような変則ループ全般をDOWループと呼ぶようになったそうです。いい話だな～、うらやましいなぁ。

僕はa matsuさんにこのブログのコメントで初めDowの書き方を教えていただきました。

さて、前置きが長くなりましたが、コードです。

proc sql noprint;
 select max(A)into:TNMAX from
 (select count(*) as A from Q1 group by X);
quit;

data A1;
informat X Y_: Z_: W_:;
 array Y_(&TNMAX) 8. ;
 array Z_(&TNMAX) $2.;
 array W_(&TNMAX) 8. ;
 do until(last.X);
  set Q1;
  by X;
  if first.X then i=0;
  i+1;
  Y_{i}=Y;
  Z_{i}=Z;
  W_{i}=W;
 end;
drop Y Z W i;
run;

で、結果は先述の通りになります。

まあ、満足。

ただ、まだ、もうちょっと面白い手順があるんじゃないかと思うのでもう少し考えてみます。

SASのデータステップについて深く勉強すると、思いついた面白い構想や、突飛なアイデアを強引に実現できる力がついてきて、結局それがモチベーションになってきてる気がします。

IMLプロシジャで全変数全obsで最大の値をとる

以前勤めていたところがSASのIMLのライセンスを導入していなかったため、IMLプロシジャを使ったことがありませんでした。
しかし、またまたSAS University EditionだとIMLが使えるということで、早速使ってみました。

僕の頁 <SASと臨床試験と雑談と>で、IMLは行列計算だけでなく、データセットの加工手段としても使えるみたいなことが示唆されていたので
「IMLによるデータセットの作成と読み込み」
http://sasboku.blog.fc2.com/blog-category-15.html

早速、前からIML使ったらどう書くんだろうと気になっていた第1回目の記事の詰めSAS一問目、
「データセット中の最大の値を1変数1obsのデータセットに格納する最善手を考えよ」
http://sas-tumesas.blogspot.jp/2013/09/sas1obs.html
という問題を考えます



さて以下のデータセットから

data Q1;
input X Y Z;
cards;
1 4 3
2 9 8
7 6 5
;
run;

から最大の値、9をとれということです。

まあ、結論としてa matsuさんの提案された

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

が一番すっきりしていて、SASのmax関数とSQLのmax関数の働きを併せているところが面白いというのもあり、最善手だと思っていますが、前々からこの問題はIMLが向いてそうだから、いつかやってみたいなぁと思っていたんですね。

以下が僕が書いたコードです。
ちょっとIMLに慣れてないので、できていないところがあればご指摘ください。


proc iml;
 use Q1;
 read all into _M;
 M=max(_M);
 create A5 from M[colname='M'];
 append from M;
quit;

結果は以下の通りです。


あ～、すっきりした！

DS2言語を初めて使ってみた話

SAS9.4から、SASの中でDS2という新しいSASのオリジナル言語が使用できるようになりました。

SAS9.4が未導入のところで働いていたので、DS2言語、気にはなっていたけども全く手を付けていませんでした。（9.4もう入れているとこってどれぐらいあるのかなぁ？）

商用利用はできませんが、SAS Univercityだと最新のSAS9.4が搭載されているので、今日初めてDS2でプログラミングしてみました。

またいずれ、きちんと勉強した後で、基本から解説する記事を書きたいと思います。
まだ、　僕自身全然わかっていないので、間違っている場合はご指摘ください。

DS2はSAS Technical news 2013 Winterでは
「DS2 とは、Base SAS 9.4で提供される新しいデータ操作言語です。DS2は、処理のパッケージ化、FedSQL の呼び出し、マルチスレッドによる並行処理など、従来の DATA ステップを拡張した機能をサポートしており、高度なデータ操作を行うことが可能です。」

と説明されていました。

まあ、何言ってるかよくわかんないですけど、とりあえず、今までのデータステップでは無理だったことが、拡張されてできるようになったよ！ってことみたいなので

今回は、1つのステップの中で、データステップと、SQL（正確にはFedSQL）とハッシュオブジェクトを組み合わせて、使用するという、ちょっとした変態コードを書いてみました。


まずは2つのデータセット

data Q1;
X='い';Y=1;output;
X='い';Y=2;output;
X='い';Y=3;output;
X='ろ';Y=3;output;
X='ろ';Y=2;output;
X='ろ';Y=4;output;
X='は';Y=4;output;
run;

data Q2;

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

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

Y=3;Z='C';output;

Y=4;Z='D';output;

run;

があったとします。

そこで、今回、Q1のXごとにYの平均値をだし、その値をキーとして、Q2のZを取得し、XとZを連結した文字列を作る処理がしたいとします。

工程を書き出すと、

①：Q1をSQLを使って集計する

②：Q2をハッシュオブジェクトに入れる

③：①で集計されたデータセットを使い、②のハッシュオブジェクトからZをfindメソッドで取り出してXとcats関数でくっつける

となります。

これをDS2で書くと

proc ds2; 

 package hash /ext='hash' overwrite=yes; run; 

 data A1(overwrite=yes); 

  declare package hash hq2(0,'Q2'); 

  declare char(10) X;

  declare double Y;

  declare char(10) Z;

  declare char(20) XZ;

  method init(); 

   hq2.defineKey('Y'); 

   hq2.defineData('Z'); 

   hq2.defineDone(); 

  end;

  method run(); 

   set { select X,mean(Y) as Y from Q1 group by X };  

    hq2.find();

    XZ=cats(X,Z);

  end; 

 enddata; 

run; 

quit; 

で結果は

となります。

なんとまあ不思議なコードですよね、部分的にはデータステップまんまだし、

SQLまんまだし、ハッシュオブジェクトまんまだし。

簡単に説明（多分、間違いが含まれているので信用しない）。

DS2でハッシュ使おうと思ったら、パッケージていうので定義する必要があるんですね。

このパッケージ化という概念がどうやらDS2のポイントになってるみたいですが、それはまた今度までにちゃんと勉強して整理しておきます。

ハッシュパッケージの宣言部分は今までのハッシュオブジェクトと大体同じですね。

ただし、そこを method init();  end;でくくっているんですが、これがね、決まり文句で、今まで

if _N_=1 then dol; end;で処理の最初だけ1度実行というデータステップの書き方に対応してるんです。

で

method run();  end; で括っているところが、これも決まり文句で、今まで普通のデータステップとして処理していた部分の書き方、つまり1obsをPDVに持ってきて処理してoutputって部分に該当するみたいです。

で

set { select X,mean(Y) as Y from Q1 group by X };  の部分ですが、

なんか、今までのSASの常識からすると、ふざけてんのかって感じで、初心者がかいちゃいそうなコードですが、DS2ではこれがまかり通っちゃうんですね～

sqlの抽出結果がそのままデータセットとみなされてsetの対象になっています。

おっさんにはついてけないですよ

 hq2.find();　と　XZ=cats(X,Z);　の部分はそのまんまですね。

さて、さらっと説明しましたが、DS2の凄さは全然こんなもんじゃないです。

サンプルなどを検索してもらえばわかりますが、なんか処理の中にfcmpプロシジャを突っ込んだようなことができて、まあ要は処理のパッケージ化なんですね。

なんだか印象としては、データステップとSQLとハッシュオブジェクトやらマクロやらユーザー定義関数の機能を、ミキサーかけて、煮込みましたみたいな感じをうけました。

先述のTechnical newsでは、DS2導入のメリットとして「複数の箇所で使用される処理のコードを一箇所で集約することで、プログラムを簡略化し保守性を高められるという利点があります」と書いています。

しかし、ちょっとだけ不安に思うのが、簡略化し保守性を高めるためには、当然、DS2言語を理解して使いこなすことが前提なわけですよね。

複数人で仕事をする場合、運用保守のためには、メンバー全員が勉強しなきゃいけないわけですが、例えば今回僕が書いた、ごく初歩的な例にしても、理解するためには、通常のデータステップの知識とSQLの知識、ハッシュオブジェクトの知識、そしてさらにDS2言語の知識が必要になるわけです。

僕のような変態が、勝手に勉強して、勝手に変態コードを量産する分には問題ないですが、これをみんなで共有するためには、トレーニングが大変だなぁと。

SASを1から教えて、ここまで理解させようとすると、時間がかかりそう..。また逆に、ベテランで、既存のステップに何十年も慣れた人にとっても、これは抵抗あるかも。

まあ、そんなこと言っていては駄目ですね！！

海外のSASユーザーは、「新しい玩具だ！ヒャッハー！」みたいなノリで、どんどんDS2の情報を発信し始めてますね。

いつもSASの新機能への反応が鈍い日本ですが、今度は頑張って張り合ってみましょう。

僕も今日から勉強します。

もし既に、実務に応用されている方がいらっしゃれば教えてほしいです

SQL専用演算子 eqt演算子について（その他のtruncated演算子についても）

SASのテクニック、というか実務者にとっての小技として、わざとエラーを出して、ログのエラーメッセージからSASに指定できるパラメータを教えて貰うというものがあります。

確か、書籍「統計解析ソフトSAS(工学社)」のどこかにも、そのことが書いてあって、わ～現場の泥臭い感じがいいな～って思いました。

さて、脱線しましたが、SQLプロシジャで色んな書き方を試して、遊んで？いた時のこと

「ERROR 22-322: 構文エラーです。次の 1 つを指定してください : !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.」

今まで何回も出したことあるエラーですが、ふと目がとまりました。

え？EQTってなによ？　あれ、GTT? は？LTT?
いや、よく見るとさらに、GET、LTE、NET、

EQはequal(=)のこと、GEはgreater than or equal to（大なりイコール　≧）ってわかるけど、
こいつらはなんなんだ！！

え～、結構SASオタのつもりだったのに、まさか、いまさら知らない演算子とか！！ショックだわ！
てか、SQL専用演算子なんかあったのか、勉強不足でした。

で、早速調べてみると、eqtは「equal to truncated strings 」とのこと、
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473695.htm

で、その他のGTTとかLTTとかもどうやら同じような系統のようです。


で、一体、こいつはどんな働きをする子かな、と試してみました。

一つ理解すれば、後はまあ同じ拡張なので、今回はeqt演算子で。

data Q1;
X='ABCDE';output;
X='BCDEA';output;
X='DCDEB';output;
run;

というデータセットがあったとして

proc sql noprint;

create table A1 as

 select X

 from Q1

 where X eqt 'AB'

;

 quit;

とすると

あ～、なるほど、指定した抽出文字列の長さ以降を切り捨てて比較ということか、

だからtruncatedね、

つまりこの場合、平たく言うと　Xが'AB'から始まる文字列ってことか。

そうするとike演算子で

proc sql noprint;

create table A0 as

 select X

 from Q1

 where X like 'AB%'

;

 quit;

と書くのと同じか。

like演算子については記事「whereステートメントでのみ使用できる演算子_contains,between,like」

http://sas-tumesas.blogspot.jp/2013/12/wherecontainsbetweenlike.html）

を参照してください（ただし、記事に誤解を招く表現があります。

この記事はデータステップのwhereステートメントでの話であって、like演算子はsqlでは、別にwhere句に限定せずに記述できますからね）

なるほど～、じゃあ例えばXが「AB」から始まる場合にフラグ変数をたてたければ

proc sql noprint;

create table A2 as

 select X

       ,X eqt 'AB' as FL

 from Q1

;

 quit;

あるいは

data Q2;
Y='BC';output;
Y='DC';output;
Y='AB';output;
run;

みたいなデータセットと以下のような条件で結合する

proc sql noprint;

create table A3 as

 select X,Y

 from Q1 inner join Q2 on

 x eqt Y 

;

 quit;

と

って感じになる。

あ～、これ結構いいですね！守備範囲せまいけど、like演算子で記述するより見た目がすっきりしてますね！

今後、gtt lttとかの使い道についても考えてみます。

でも、これって、知らない人がコードレビューしたら、絶対ミスタイピングだと思われそう、、、。

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→全オブザベーションがそのままでる

　　極値→×

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

SQL：自己結合、自己非等値結合で直積から組み合わせまで

以前、
「ｎ個の変数からm個の変数を選択する、組み合わせのデータセットを作成する方法」
http://sas-tumesas.blogspot.jp/2014/01/m.html

では、データステップで4C2の組み合わせデータセットを作りましたが、
今回はSQLで同じことをやっていきます。

自己結合は1つのデータセットを、まるで同じものが複数あるかのように見立てて結合する
やり方で、生粋のSAS使いには苦手な人が多いような気がします。

data Q1;
X='い';output;
X='ろ';output;
X='は';output;
X='に';output;
run;

今回はまず、上図のような形で縦に値を持たせたデータセットを作ります。


そして、まず直積の説明（cross joinでも可）

proc sql noprint;
 create table A1 as
  select T1.X as X1
        ,T2.X as X2
  from Q1 T1,Q1 T2;
quit;

結果は

となって、4掛ける4で16オブザベーションになります。
自己結合の場合、変数がどっちのテーブル由来（同じテーブルなのでどっちとかってのも変ですが）
か記述できなくなるので
Q1 T1のように「テーブル名　半角スペース　別名」として、Q1テーブルにT1という仮名を与えます。
Q1 as T1のようにas使ってもＯＫです



これだと、「い」「い」のように同じ値を2回使ったりします。

次に、同じものが2度現れない順列で
4P2を表現するなら、同じ値の出現を消せばいいわけなので

proc sql noprint;
 create table A2 as
  select T1.X as X1
        ,T2.X as X2
  from Q1 T1,Q1 T2
  where T1.X^=T2.X;
quit;

とすれば

のように12オブザベーションになります。

ただ、これだと「い」「ろ」と「ろ」「い」のように、順番を並び変えただけの組み合わせ的には
同じものが含まれます。


そこで、 where T1.X^=T2.X;としているところをwhere T1.X<T2.X;としてしまいます。
文字列に不等号使うのに違和感を感じるかもしれませんが、文字の出現順で大小決まるだけなので数字と変わりません。
要するに、2パターンでてこられると困るので、不等号にしとけば絶対1パターンしかでないでしょって感覚なわけです。

proc sql noprint;
 create table A3 as
  select T1.X as X1
        ,T2.X as X2
  from Q1 T1,Q1 T2
  where T1.X<T2.X;
quit;


結果は

6オブザベーションで、正解です。

このあたりの話は以前SQL関連の書籍を紹介した中の
http://sas-tumesas.blogspot.jp/2013/10/sassql.html


達人に学ぶ SQL徹底指南書
　著者：ミック
　出版社:翔泳社 (2008/2/7)

を参考にしています。

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

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

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カ月でネタ切れ閉店の予定が思いのほか長く続いてます

自動SQL変数？ userで、SAS実行ユーザーの情報をマクロ変数を使わずにゲットする話

自動マクロ変数ってあります。

ユーザーが値を入れなくても、勝手に決まった名前で作成されているマクロ変数です。

%put _automatic_;

って書いて実行すればログに溢れてきます。
なにせ自動で作成されているので、利用すると労力削減になるケースが多くて万歳です。


例えば、データセットを作成するプログラムがあったとして、
そのデータセットを誰が作ったかをデータセットに変数の値として持たせたいとします。

data A;
 X=2;
run;

自動マクロ変数&SYSUSERIDを使えば、SASセッションを開始した

ユーザー（PCに設定されているユーザー名）が簡単に取得できます。

data B;

set A;

 X=X*2;

 NAME="&SYSUSERID";

run;

これで、いちいちログ見なくても

SASYAMAがデータセットBを作成したってことが一目瞭然です。

（データセットに作成者全レコードに持たせるなんてマジ無駄ですが）

で、これを同じ様にSQLで書くと

proc sql;

create table C as

select X*2 as X

       ,user as NAME

from A;

quit;

とかけます。

この「user」っていうのが、別にマクロ変数でもないのに、固有の変数名で

&SYSUSERIDと同じ値を返します。

でも当然

proc sql;

create table D as

select X*2 as X

       ,"&SYSUSERID"as NAME

from A;

quit;

も通るので、あんまりお勧めできる書き方ではないかも

（ぱっと見、知っている人じゃないと意味が取れないし）

意地でもマクロ変数をコードから排除してやるって方はどうぞ。

ちなみに僕は必然性がない限り極力マクロは使わない派です。

マクロを使うべきところに使っているのは当然いいですし、マクロなしで

SAＳプログラミングは成立しないと思っています。

しかし、他人様のSASコードを見ていると、SQLで簡単に処理できたり

class や by ステートメントやtransposeプロシジャをうまく使えば

簡単なコードと最小ステップでまわせるような処理を

わざわざ細切れのマクロにして、グルグル回していたりして、

それはSASの良さを殺してんじゃないかしら、、と思ったりして。

かなり偉そうになりましたのでこの辺まで。

多分、複雑なマクロ組めないのでひがんでいるんですね

文字操作関数の戻り値がデフォルトで$200になるルールは必ずしも成立しないから気をつけろという話

いつも以上にマニアックな話ですが、最近発見したことを書きます。
場合によっては予期せぬ結果を生む可能性のある仕様についてです。

ログにあるメッセージをだしたいので、まず　msglevelをi　にしておきます。
options msglevel=i;


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

data Q1;
X='A';
run;


そこで

data A1;
set Q1;
Y=repeat(X,100);
Z=repeat('A',100);
run;

と書いて実行します。

ＹもZもやっていることは同じで、文字値'A'を100回繰り返して
「AAAAAAAAA・・・・・・」といった値を入れています。

Y,Zについて、lengthを指定していないため

いつものようにデフォルト$200ルールが適用されます。

さて、ここまではそれでいいのですが、次に

proc sql noprint;
create table A2 as
 select repeat(X,100) as Y
        ,repeat('A',100) as Z
 from Q1;
quit;

を実行するとどうなるでしょうか？

僕はてっきり、先のコードと全く同じ結果になると思っていましたが実際は

変数Ｘ由来のYについては長さは200ですが
定数から直に作ったZの長さは200になってません！データステップなら
なっていたのにです！
ちなみにログには何もでません。


SQLで、多数の抽出結果をunionで縦に繋ぎまくる処理を書いている際、
文字切れが生じていて初めて発見しました。


9.2から、lengthの違う変数をSETで縦結合した際、「WARNING」がでるようになりましたが
（ちなみにoptions varlencheck=nowarnででなくもできまし）、

SQLプロシジャ内のunionだと文字切れが生じていてもおかまいなしなので
ゆめゆめ気をつけましょう。

文字切れは怖い

頭の体操 集合に対する抽出

例えば

data Q1;
TEAM='A';ID='aさん';VAL=90;output;
TEAM='A';ID='bさん';VAL=35;output;
TEAM='A';ID='cさん';VAL=42;output;
TEAM='A';ID='dさん';VAL=56;output;
TEAM='A';ID='eさん';VAL=68;output;

TEAM='B';ID='fさん';VAL=40;output;
TEAM='B';ID='gさん';VAL=58;output;
TEAM='B';ID='hさん';VAL=62;output;
TEAM='B';ID='iさん';VAL=52;output;
TEAM='B';ID='jさん';VAL=68;output;

TEAM='C';ID='kさん';VAL=90;output;
TEAM='C';ID='lさん';VAL=88;output;
TEAM='C';ID='mさん';VAL=62;output;
TEAM='C';ID='nさん';VAL=32;output;
TEAM='C';ID='oさん';VAL=38;output;

run;

のようなデータがあったとします。

A B Cの3チームで、チーム内には複数のメンバーがいて変数VALには何らかの得点が
入っているとします。


ここで、チームの70%以上のメンバーが50点以上の得点であるグループのTEAMを抽出して
データセットにしたいとします。

さて、どうしますか？

if VAL>=50 then FL=1;とかデータステップで一度付与してからfreqなんかで集計するのも手ですが、この手の問題については、やはりべらぼうにSQLが強いです。集合指向言語の肩書は伊達じゃないです。


つまり

proc sql ;
create table A1 as 
 select TEAM
 from Q1
 group by TEAM
 having count(*)*0.7<=sum(case when VAL>=50 then 1 else 0 end);
quit;

で詰んでるんですね。

【追記】
しまった！真偽ルールを使えば

proc sql ;
create table A1 as 
 select TEAM
 from Q1
 group by TEAM
 having count(*)*0.7<=sum(VAL>=50);
quit;

これでいけるんだった。くそっ