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

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








11 件のコメント:

  1. どうも、SASNamiです。ご無沙汰しております。

    今年の総会に発表する流れになってしまい、本投稿を使わせて頂きたいと考えております(というか、ほぼそのまま使います)。VBAからのSAS実行とExcel Libnameは無限の可能性を秘めており、私もまだまだ勉強しないといけない立場ですが、これからも新しい発見楽しみにしております。

    参考文献としては、御ブログ、社会科学ユーザー会等もありますが、昨年の総会での発表を記載させて頂く予定です。

    返信削除
    返信
    1. どうもです!こちらこそご無沙汰しております。

      ユーザー総会でられるんですね!!嬉しいです!!
      めちゃくちゃ楽しみです!
      こんなブログでよければいくらでも使ってください。参考文献もどれをどんな風に記載していただいても大丈夫です。
      悪い例として、こきおろしてもらってもいいくらいです。

      LIBNAME、、なかなかうまくいかないところや謎仕様もあって歯がゆいですが、僕も頑張ってます。
      実は僕も今年、2本論文だすことになって息も絶え絶えです。ハッシュオブジェクトと、もう一本はLIBNAME EXCEL&数独のコラボ論文になります。
      本当はもう一つ、kifファイル形式という将棋の棋譜を表現する特殊な拡張子のファイルがあるのですが、これをSASで解析する方法を研究しているのですが、ちょっとそれは間に合いませんでした。

      あと、最近面白いなぁと思って研究しているのが、VBAからのSAS実行ではなく、SASからのVBA実行で、これが結構いけます。
      名前の範囲を消さずに値をクリアするときにVBAでやった方が何かとよくて使ってます。VBAでdeleteというマクロを作っておいてfilenameで定義してputでぶちこみます。

      filename cmds dde 'EXCEL|SYSTEM';
      data _null_;
      file cmds;
      put'[RUN("delete")]';
      run;

      好き勝手しゃべってしまいましたので、この辺で。

      ブログも楽しみにしております

      削除
    2. 早速のお返事ありがとうございます。
      2本ですか、すごいですね。。今から楽しみにしております。

      あと、SASからVBAですか、どんどん新しいネタが出てきますね。
      昔見てこれは無理だと食わず嫌いになった記憶があります。

      私の発表はExcelのデータ仕様書とVBAによるSAS実行、Libname Excelによるパラメータ受け取りをCDISCと絡めてお話しようと思っております。ただ全然大したことはやらないので期待しないでください。

      ともあれ、総会でお会いするのを楽しみにしております。

      削除
  2. ある企業SASユーザ2015年6月3日 21:52

    始めて投稿させていただきます。
    予めセルに名前を定義したExcelに、libnameを使ってSASからデータを渡そうとしています。これはまさにこちらのブログや2013総会でのご発表の内容がそのまま使えると思って試してみました。しかし、色々やってみましたが、上手くいきません。なぜか、名前定義済みのセルではなく、そのセルが存在する列の1行目のセルに必ずデータが表示されてしまいます。

    2014総会の論文集には、使用するSASのバージョンとExcelのバージョンの組み合わせ、又はその他の実行環境の違いによって実行時の動作が一貫しないことがあるとの記載がありますが、これがその一例に当てはまってしまっているのでしょうか?

    因みに当方の環境ですが、SAS9.3とExcel2007はWindows Server 2003上にインストールされており、それをCitrix経由で使用しています。

    何かアドバイス等ありましたら助かります。
    宜しくお願いします。

    返信削除
    返信
    1. コメント有難うございます!そして、論文まで読んでくださったみたいで本当に有難うございます!

      SAS9.3とExcel2007の組み合わせは恐らく問題ないはずです。Citrix経由では使ったことがないので、ちょっと微妙ですが。

      もしからしたら、違うところに問題があるのではと思いますので、お手数ですが
      sasyupi@gmail.comまで、該当のEXCELファイルとsasプログラムを送っていただけませんでしょうか?

      あるいは掲示板に、投稿していただいて、そこに添付ファイルでアップでもかまいません。
      http://tumesas.progoo.com/bbs/

      削除
    2. ある企業SASユーザ2015年6月10日 9:43

      実はあれからSAS社のテクニカルサポートの方と色々やりとりをしています。SAS社でも現象を確認できたそうです。いわく、Citrixは関係なくて、SAS9.3の32Bit版で確認できたとのことでした。

      現在米国本社に回避方法を問い合わせしてもらっており、その回答待ちです。

      また何か分かりましたら報告させていただきます。

      削除
    3. おぉ!そうなんですね。
      しかし、SAS9.3の32Bit版でも動いてたんですけど、なんか細かいバージョンの違いですかね…。
      libname EXCEL 便利なんですけど、そういう環境差異がつらいんですよね。
      宜しくお願いいたします

      削除
  3. ある企業SASユーザ2015年6月22日 15:36

    まだ中間報告の段階ですが、どうやら、Excelの名前定義通りデータが送れないのはACEエンジンだからだそうで、JETエンジンを使うよう助言をいただきました。即ち、xlsxではなくxls形式のExcelが相手になります。これでやっと意図したセルにデータが送れるようになったのですが、別の問題が出てきました。それは文字数制限です。256文字以上だと全くデータが送れません。回避方法を再度米国本社に聞いてもらっています。

    返信削除
    返信
    1. ある企業SASユーザ2015年7月13日 18:18

      米国本社から最終の回答が届きました。256以上の文字を送る場合はDDEを使用するという結論になりました。いつ消えるかわからない(でもなかなか消えない)枯れた技術であるDDEに暫く頼ることになりそうです。

      削除
  4. こんにちは。SASのegpとエクセルマクロを交互に実行する処理があるのですが、行き詰まっております。
    プロジェクト実行完了したら、VBAを実行し、VBAが実行完了したら、別のプロジェクトを実行と繰り返すことは可能でしょうか。
    もし、ご存知でしたら教えていただけると助かります。

    返信削除
  5. 有難うございます.あー,ちょっと私がEnterpriseをわかっていなくてno ideaなのです.ちょっと知ってそうな人たちにもきいてみます.とりま,lexjansenで,それらしいものをサーチするぐらいでしょうか…
    https://www.lexjansen.com/search/searchresults.php?q=egp+vba

    返信削除