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のように枯れた知識になってない感じがあります。

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

PLANプロシジャとMEANSプロシジャ・CLASSDATAステートメントのコラボで欠損水準を0で補完した集計を行う

最初に、今回のプログラムにあえてproc planを使用する必然性はありません。ただ、他の目的を持ったプロシジャも視点を変えると思ってもみない用途に使えるということを示す例、まぁ詰まる所はお遊びなんですが。

さて、PLANプロシジャは本来、実験計画・割付に使用するプロシジャですが今回は、MEANS(SUMMARRY)プロシジャのCLASSDATA=オプションで指定する水準データセットをproc planで作ってみます。

いま、

data Q_1;
 LEVEL_1=1;LEVEL_2='B';FLAG=1;output;
 LEVEL_1=1;LEVEL_2='B';FLAG=1;output;
 LEVEL_1=2;LEVEL_2='C';FLAG=1;output;
 LEVEL_1=3;LEVEL_2='A';FLAG=1;output;
run;

こういうデータセットがあって、LEVEL_1はとりうる値が1-3、LEVEL_2はA-Cだという前提条件があって、以下のように、出現していない水準の組み合わせを0で集計したい場合があるとします。

さて、まずはproc planで、水準の情報を与えて、とりうる組み合わせのフルセットがはいった

データセットを作成します。

proc plan;

 factors LEVEL_1=3 LEVEL_2=3 ordered /noprint;

 output out=CLDS

  LEVEL_2 cvals=('A' 'B' 'C');

run;

quit;

randomステートメントなしのproc planはなんだか斬新というか違和感ありありですが、

上記の結果、生成されるデータセット「CLDS」は

こうなります。3水準×3水準なので9水準がフルセットですね。

そこで後は普通にmeansプロシジャのclassdata=で上記のCLDSを指定すれば

proc means data=Q_1

            classdata=CLDS

            noprint nway exclusive;

            class LEVEL_1 LEVEL_2;

            var FLAG;

            output out=A_1(drop=_TYPE_ _FREQ_) N=COUNT;

run;

となって完成です。

なおmeans プロシジャのclassdata=についてはまた、いつか改めて説明しますが

classdata=で指定のデータセットとdata=のデータセットのclassステートメントで指定された変数の

長さ、ラベル、フォーマット、インフォーマットが同一でないとエラーになるので

集計したいデータセットにメタデータが定義されている場合は

proc datasets nolist;

 modify Q_1;

  attrib _all_ informat= format= label='';

quit;

で定義情報をリセットしておくことをお勧めします。

ちなみに、、あえてPLANプロシジャつかわなくて

data CLDS;

 do LEVEL_1=1 to 3;

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

   output;

  end;

 end;

run;

で、いいので、こっちの方がよいかもしれません。

ただ、現実での実践例が思いつきませんが、ランダムに決定される水準セットによって

集計するような処理を書く場合（どんな場合だ？？）、ploc planならスムーズかもしれませんね

（その場合でもデーステップで rand関数で、classデータ作成してやりゃいいんですけどね）