EXCELを媒介にして、漢字をカタカナに無理やり直す方法

日本語は、やはり母国語なので言語としては愛着があるのですが、ことプログラムで処理するとなると、これほど厄介な言語って他にあんのかな？って気になってしまいます。

名寄せ処理する上で大問題なのが、日本語は、漢字・ひらがな・カタカナ（＋数字や記号）が混在しているということです。

最近、受けた質問ですが、例えば以下の2つのデータセットがあったとします。

data Q1;
length TERM $10.;
 TERM='豆腐';output;
 TERM='アズキ';output;
 TERM='だいず';output;
run;

data Q2;
length TERM $10.;
 TERM='大豆';output;
 TERM='とうふ';output;
 TERM='小豆';output;
run;

これで、この2つのデータセットを同じ言葉同士でマージしてと言われたらどうしますか？

人の目であれば、「豆腐」と「とうふ」が同じ言葉であることは一目了然なのですが、これを機械に
わかってもらうのはとても大変です。

たとえば、これが漢字を含まない、ひらがなとカタカナだけのデータであれば
kpropcase関数で、いずれかに統一させてマージができます。

kpropcaseについては色々な方が説明されているので、そちらを見て下さい

【SAS忘備録】
http://sas-boubi.blogspot.jp/2013/12/blog-post_16.html

【SAS Utility】
http://sasutility.blogspot.jp/2010/07/blog-post.html

【SAS社Q&A】
http://www.sas.com/offices/asiapacific/japan/service/technical/faq/list/body/ba268.html


ところが、漢字の変換になると、SASだけでは（僕の知る限り）変換処理する機能がありません
（多分ですが、、、。もしあったなら教えてください）

なので何かしら外部アプリケーションの力を借りるしかないはずです。

SASの中でCやJAVAなんかのコードを利用できるらしいので、そっちを使うか或いは、
proc httpなどでweb上とやりくりができるので、yahooが提供している「かな漢字変換API」
http://developer.yahoo.co.jp/webapi/jlp/jim/v1/conversion.html
などにデータを送って、変換されたものを受取るということもできるはずです。


しかし、いずれの方法をとるにせよ、SASとは別の知識が要求されるので、他の言語ができないと結構ハードルは高くなります。


そこで、まだちょっとは馴染みのあるEXCELさんに奴隷のように働いて貰いましょう。
EXCEL VBAも別の言語ですが、まぁまだ簡単でしょう。

ちなみに、これから紹介する方法は、もはやプログラミングではない！って感じの乱暴なものですのであしからず。


まず、適当な名前でマクロ付のexcelファイル(xlsm)を作成します。
ここではCドライブ直下に、BOOK1.xslmという名前でつくっています。

そして標準モジュールに

Function getphonetic(a)
getphonetic = Application.getphonetic(a)
End Function

と書いて、ユーザー定義関数を定義します。

このApplication.getphoneticは変換候補の一番目にくるカタカナを取得します。

なので、先に断っておきますが、必ずしも正しい読み仮名であるとは限りません。人名・地名や

読み方のたくさんある言葉、一般的ではない言葉の場合、思いどおりにいかないこともあります。

さて、関数が定義できれば

次にSheet1でいいので、1列目2列目を全選択して、名前を定義します。ここでは「AREA」としています。

さて、もう何をしようとしているか気付かれたと思いますが、ここで2列目の全セルに

「=ＧＥＴPHONETIC(RC[-1])」という関数を入れます。

これによって1列目のセルに何か文字をいれたら、2列目でカタカナ変換された値がでてくる

作業シートができました。

あとはもうlibnameの世界ですね。

一気に全コードを載せます。

libname EX "C:\Book1.xlsm" header=no scantext=no;

data EX.'AREA'n;

 set Q1;

 modify EX.'AREA'n;

 F1=TERM;

run;

data _Q1;

length TERM1 KANA $10.;

 set EX.'AREA'n;

 where F1^='';

 TERM1=F1;

 KANA=F2;

keep TERM1 KANA;

run;

data EX.'AREA'n;

 set Q2;

 modify EX.'AREA'n;

 F1=TERM;

run;

data _Q2;

length TERM2 KANA $10.;

 set EX.'AREA'n;

 where F1^='';

 TERM2=F1;

 KANA=F2;

keep TERM2 KANA;

run;

proc sort data=_Q1;

 by KANA;

run;

proc sort data=_Q2;

 by KANA;

run;

data A1;

informat TERM1 TERM2; 

 merge _Q1 _Q2;

 by KANA;

run;

で結果のA1の中身は

というわけです。

ちなみに途中の_Q1の中身は

_Q2は

です。

さて、この方法はお手軽で、exceｌさえあればできますが、問題もあります。

まず、こういった名寄せ処理は扱うデータ数が巨大な場合が多いですが、この方法だと

excelシートの行の限界数までしかできませんし、変換のためだけに無駄にシートに読み書きして

いるので、もう馬鹿みたいに遅いです。重すぎて止まっちゃうかも。

また、普通にタイピングしていて変換して1番にくる文字を選ぶだけなので、データの文脈おかまいなしです。変換結果を目でチェックしたほうが無難です。

さて、悪口ばかり言いましたが、大した量でなくて、ちょっとくらいミスがあってもいいような名寄せならこれで充分実用に耐えうります。

来年の論文の一部にしようかな。

xステートメント(xコマンド)でファイルのコピー

外部ファイルをコピーするxコマンドについてよく忘れるので自分のためにメモです。
xコマンドはSASからOSにお願いをして、指定の命令を実行してもらうような機能です。
options noxwait;をつけないとコマンドプロンプトの黒画面が残ります。


options noxwait; 

x 'copy "D:\sample\AA.xlsx" "D:\sample\BB.xlsx"';

AAというエクセルファイルをコピーしてBBというエクセルファイルを作成します。

例えばアンケート調査をする時なんかに
まず、コピー元になるひな型アンケートフォームをエクセルで作って、
必要なexcel関数式や入力規則、名前の定義を指定しておきます。

そしてそれをlibnameで読み込むプログラムを書きます。

xコマンドをうまくマクロループで利用して、調査ID分、ひな型ファイルをコピーしまくる。

アンケートをばらまく→エクセルに記入してもらってそのファイルを回収
→読み込むプログラムをマクロループで繰り返して全部いっぺんにデータセット化みたいな風にすると、入力業務が省け、読み込みエラーも少なく、楽だと思います。
エクセルファイルをアクティブにせずにアクセスできるので、実行速度的にDDEより早いはずです。

ファイルの場所や名前にマクロ変数を入れる場合、クォート周りに要注意。

x "%str(copy %"&PATH.\BASE.xlsx%" %"&PATH.\COPYOUT.xlsx%" )";

LIBNAME EXCELで「予期しないエラー~」とでる場合はファイルの属性をチェックし、access=readonlyで解決する場合がある

最近で直接いただいた質問のうち、2件同じ症状で、同じ解決法で解決した事例の紹介です。

libname　EXCELでlibnameステートメントを実行した時点で以下のような

ERROR: Connect: 外部データベース ドライバ (????????) で予期しないエラーが発生しました。

や

ERROR: Connect: 外部データベース ドライバ (am Files\Common Files\Microsoft Shared\OFFICE12\ACECORE.DLL) で予期しないエラーが発生しました。

といったエラーメッセージがでる場合、EXCELファイルのプロパティの属性が
「読み取り専用」となっている可能性があります。

その場合libnameステートメントに

libname XX "----------" access=readonly;

と読み取り専用でライブラリ指定しないと上記のエラーになります。
エラーメッセージから意味が読み取りにくいのはいつものことですが、これも気づきにくい
エラーですね。

読み取り専用のチェックはずしてもいいなら外してもＯＫです。

LIBNAMEとVBAで、EXCELとSASをつないで対話的アプリケーションにする

EXCELにはEXCEL VBAという便利な言語がくっついているので、これを使ってEXCELからSASを実行することができます。

またSASのLIBNAME EXCEL機能を使えば、EXCELの値を自由に読み込み＆書き込みできます。

この二つを組み合わせれば、みんな大好きエクセルさんをインターフェースにして、そこに
ユーザーが入力した値をSASに渡して、処理を行い、処理結果をエクセルに返すことができます。


その簡単な例を紹介します。
今、「D:\FACE.xlsm」というマクロつきのエクセルブックがあったとします。
黄色の部分にそれぞれの計算結果が入れたいとします（黄色セルは表示形式：数値）

そして、「D:\計算.sas」というSASファイルがあり、
その中身は以下のコードだとします。

libname EXLIB "D:\FACE.xlsm" header=no scan_text=no;

data Q1;
 set EXLIB."Sheet1$D8:D8"n ;
 rename F1=X;
run;

data Q2;
 set EXLIB."Sheet1$F7:F7"n ;
 rename F1=Y;
run;

data Q3;
 set EXLIB."Sheet1$F9:F9"n ;
 rename F1=Z;
run;

data A1;
 set Q1;
 set Q2;
  A=X+Y;output;

  A=.;output;

 set Q1;
 set Q3;
  A=X+Z;output;
run;

data EXLIB."Sheet1$H7:H9"n ;
 set A1;
 modify EXLIB."Sheet1$H7:H9"n ;
  F1=A;
run;

何をしているかというとエクセル数字の入っている3セルを読み込んで

計算結果を黄色のセルに出力しているわけです。

で肝心のEXCELからSASを起動する方法ですが

「計算ボタン」に以下のコードを結び付けています。

Sub ボタン1_Click()

Dim sasobj As Object

Set sasobj = CreateObject("SAS.application")

sasobj.Visible = False

sasobj.Submit ("%inc 'D:\計算.sas';")

sasobj.Submit ("ENDSAS;")

End Sub

これだけです。

実行ログファイルが欲しい場合はprinttoプロシジャ等をいれましょう。

以上の準備が整えば、あとはエクセル上のボタンをクリックすれば

と一瞬で計算結果がエクセルに返ってきます。

endsasをいれているので、SASも勝手にとじます。

この一連の流れは結構使えると思います。

あと、これは勝手なお願いですが、LIBNAME　EXCELは便利で、DDEにはない長所をいっぱい

持っているんですが、いまいちまだ解らない部分や、誰も試していない部分が多くて

DDEのように枯れた知識になってない感じがあります。

新しく発見された部分があれば、どんどん共有していけたらと思います。

水準がデータで揃わずスカスカの集計表に立ち向かう_meansやsummaryのclassdata=とtransposeのid 複数変数を利用して

以前、meansまたはsummaryプロシジャのclassdata=オプションは集計表を作成するうえで役立つと言い詳細はまた紹介すると書きました。
（ちなみにmeansとsummaryの違いは、デフォルトでアウトプット出力するかどうかです。つまりmeansにnoprintをつければsummaryと同じで、逆にsummaryにprintをつければmeansと同じです。）

またtransposeプロシジャのIDステートメント複数づけのおかげで、集計表が作りやすくなったとも言い、実例をいつか紹介すると書きました。

遅くなりましたが、ざっくりとした流れを紹介したいと思います。

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

data Q_1;
GROUP='A';SUBGROUP='X';LEVEL1=2;LEVEL2=1;LEVEL3=5;output;
GROUP='A';SUBGROUP='Z';LEVEL1=1;LEVEL2=0;LEVEL3=4;output;
GROUP='B';SUBGROUP='Z';LEVEL1=1;LEVEL2=0;LEVEL3=3;output;
run;

で、GROUPはＡ群とB群がいて
それぞれのグループにSUBGROUP X、Y、 Zがあるとします。
そして、3つの変数があり
LEVEL1は1-2の値をとります。
LEVEL2は0-1の値をとります。
LEVEL3は3-5の値をとります。

ところが、今まだデータが集まっていない、またはデータの収集が打ち切られた等の
理由で、たった3オブザベーションしかありません。

このわずかのデータで

のようなEXCELの集計表テンプレートに出力する必要があるとします。
つまり、大半のセルは0になります。

どんな言語でも、あるデータをだすのは簡単ですが、ないデータをだすプログラムは難しいものです。とりあえず先にコード全部のせます。

data CLDS;
 do GROUP='A','B';
  do SUBGROUP='X','Y','Z';
   do DUMMY=0 to 20;
    output;
   end;
  end;
 end;
run;

/*================================================
マクロ名：syogi
引数-①dsname 対象データセット
　　 ②varname 対象変数
　　 ③minval 対象変数のとりうる最小のカテゴリ数値
　　 ④maxval 対象変数のとりうる最大のカテゴリ数値
=================================================*/
%macro syougi(dsname,varname,minval,maxval);
 proc means data=&dsname.
     classdata=CLDS(rename=(DUMMY=&varname.) where=(&minval.<=&varname.<=&maxval.))
     noprint nway exclusive;
     class GROUP SUBGROUP &varname.;
     var &varname.;
     output out=&varname._(drop=_TYPE_ _FREQ_) N=COUNT;
 run;

 proc sort;
  by &varname.;
 run;

 proc transpose data=&varname._ out=_&varname.(drop=_NAME_ rename=(&varname.=VAL))  delimiter=_;
  var COUNT;
  id GROUP SUBGROUP;
  by &varname.;
 run;
 %mend;

/*実行*/
 %syougi(Q_1,LEVEL1,1,3)
 %syougi(Q_1,LEVEL2,0,1)
 %syougi(Q_1,LEVEL3,3,5)

 data A_1;
  set _:;
 run;

/*EXCEL出力*/
 libname OUTEX "D:\集計.xlsx" header=no scan_text=no;

data OUTEX."Sheet1$D11:I18"n ;
 set A_1;
 modify OUTEX."Sheet1$D11:I18"n ;
  F1=compress(put(A_X,best.));
  F2=compress(put(A_Y,best.));
  F3=compress(put(A_Z,best.));
  F4=compress(put(B_X,best.));
  F5=compress(put(B_Y,best.));
  F6=compress(put(B_Z,best.));
run;

libname OUTEX clear;

まず、CLDSというのを作っています。
これはクラスデータセットといって、meansのクラスで指定する変数の、フルパターンをクラスデータセットに作成しておくことで、カウントであれば実際のデータの水準が足りなくても0で補完して計算してくれます。

ただ、1つの変数に対しての集計ならいいのですが、複数で、かつ採りうる値が変数ごとに違う場合、変数分それに対応するクラスデータセットをつくってたら煩雑すぎます。

なので、通常、群やサブグループなど固定のクラス変数は固定して、それ以外のカテゴリ値の部分は一端ダミーの変数名にして、大きめにデータセットを作っておきます。

以下がCLDSの中身（一部です）です。

（一部です）


それで、マクロの中で、実際に集計する対象の変数を指定して、ダミー変数をその変数名にrenameして、かつそのカテゴリ値の採りうる値でwhereでクラスデータセットを絞っています。
以下が、マクロにLEVＥＬ1を指定した時の、meansからoutされたデータセットです。
欠損水準を0で補って、フルセットで集計されています。

しかしこのままだと縦持ちなのでこれをグループ、サブグループで転置します。

以下は上記のデータセットがtransposeされた後のデータセットです

delimiter=でアンダーバーを追加しています。
たとえば、A_XはグループＡのサブグループXの集計結果というわけです。

それでLEVEL1-3までを集計してその結果をコロンモディファイア指定でSetしてつなぎます。
最後にLIBNAME　EXCELでだしていますが、DDEでもなんでもいいです。

ざっくりとした例なので、実際に使用される場合は、適宜書き換えてください（とりあえずマクロ名変えましょう）。そのままべたっと貼っても動きません多分。

で注目点はクラスデータと、実際のデータのフォーマットやラベルなどのメタデータが同期していることが必要なのでLENGTHを合わせたり、

proc datasets nolist;

 modify Q_1;

  attrib _all_ informat= format= label='';

quit;

などで、余計なメタデータを消しておいた方がいいです。
classdata=使うといつもエラーになって、あきらめますという話を聞くのですが
大半はここが原因です。

あと最後に、若干ネタ切れ感がでてきたので、取り上げてほしい部分や、詰めSASの問題など
アイデアがある方はご連絡ください