Excel ファイルのヘッダとフッタの設定ユーティリティ

しばらくぶりの更新です。仕事がらSAS以外で使うユーティリティを作りました。Excelファイルのヘッダ、フッタ、ブックのプロパティを一括で更新するユーティリティです。提出物、納品物の体裁を整えるときに面倒なのが、ヘッダ/フッタの設定です。

同じような機能をもったフリーウェアはありますが、ヘッダ/フッタの設定値を保存できない、複数ファイルを指定できないなどの制限があったので、自分が仕事で使う目的で作りました。

私は、ヘッダ中央にファイル名、フッタ左側にシート名、フッタ右側にページ番号をセットするようにしています。出力して紙で持ち歩くときに、ファイル名がわからず、フォルダの中を探し回ることが無いように、ヘッダにファイル名を入れています。

設定するExcelファイル、ファイル選択のダイアログ又はドラッグ&ドロップで選択できます。年末年始休暇の間に微調整して、Vectorに登録します。


バージョン情報の画面

 ヘッダー/フッターの設定画面

ページの設定画面

プロパティの設定画面

欠損値が生成

忘れそうなので覚書きです。
SAS Log Utility 1.2.0.0では欠損値の生成はチェックしません。これはいまの仕事の都合で、既存のコードがわんさかと欠損値を生成するからです。

NOTE: 欠損値を含んだ計算により、以下の箇所で欠損値が生成されました。
NOTE: Missing values were generated as a result of performing an operation on missing values.


直すべきか思案します。

次のリリース 1.2.0.0

SAS Log Utilityに機能を追加しました。 後一週間ほどでリリースします。

• ログファイルをドロップで選択できるように改良
• ログファイルからバグになりそうなメッセージを拾ってカウント

バグになりそうなメッセージとは以下の5つです（日本語版もOK）。これは、SASプログラムのテスト、受け入れをするときになんとなくチェックしていた項目です。他にチェックポイントが無いか思案しています。

• NOTE: Variable x is uninitialized.
• NOTE: Numeric values have been converted to character ...
• NOTE: Character values have been converted to numeric ...
• NOTE: MERGE statement has more than one data set with repeats ...
• One or more lines were truncated.

画面イメージを下に貼り付けます。チェックに引っかかった行が色つきで表示されます。受け入れテスト、エビデンスのチェックに使う予定です。

よくある潜在的な問題をチェック

ログに含まれるバグになりそうなメッセージを拾ってくれたら、ケアレスミスを防げるかも。ついでに、ファイルのドロップの仕組みも作っています。開発中の画面です。

変数が初期化されていません。

忘れそうなので覚書きです。
変数名を間違えて初期化されない、マージでデータが重複する、切り捨てられた行があるなど、よくあるミスをチェックしてくれる機能がほしいです。自分で作るか？


NOTE: 変数 x は初期化されていません。
NOTE: 以下の箇所で数値を文字値に変換しました。
( 行 : カラム )
267:11
NOTE: 以下の箇所で文字値を数値に変換しました。
( 行 : カラム )
268:11
NOTE: MERGE ステートメントに BY 値を繰り返すデータセットが複数あります。

1.1.0.0 をリリース

バグ修正、メッセージの修正を行ったバージョン 1.1.0.0 をまもなくリリースします。 修正したのは次の点です。

• マクロでないデータステップにマクロ名がつくバグを修正
• CRUD(Detail）で右端のファイルが1件欠けるバグを修正
• 解析、エクスポート中にカーソルを砂時計に変更
• ファイルがオープンできないときのメッセージを変更

このユーティリティの更新は一度停止し、仕事に役立つ他のツールを検討します。

1.0.5.0 がリリース

Vectorに新しいバージョンがリリースされました。今回はマイナーな修正で、CRUD(detail)の表にマクロ名を加えています。

• CRUD(detail)の表示項目にマクロ名を追加
• CRUD（summary)の表示項目からDataStep/PROCを削除

CRUD表にマクロ名を表示してみる

CRUD表にマクロ名を表示してみた。ちょっと考え込んだのは、マクロを呼び出すとき最後にセミコロンをつけるかどうかです。コメントアウトしやすいように %foo; とか書くと、マクロの範囲が判定しにくくなります。これは、何も行わない空文が展開されるからです。

素直にMPRINTだけ補足するだけでは足らず、マクロの終端を判定するロジックが必要になります。厳密な構文解析は行わず、空文が展開されたらマクロの名称をクリアするようにして解決しています。

エクスポートの機能をチェックしたら、マクロ単位で入出力を集約したCRUD表を作ります。

次の機能拡張を検討中(1.0.5.0)

1.0.4.0のリリース依頼は申請しました。ほどなく、Vectorで公開されます。

マクロ単位の処理時間集計は、便利で私の仕事でも役立っています。というか、私の仕事で役立つツールを作っているのが実態です。

次の機能は、CRUD表にマクロの名前を入れたいと考えています。まずは、マクロの名前を縦軸に入れる。うまくいけば、マクロ単位で集約したCRUD表にしたいと考えています。で、その先は...ドキュメント化ですね。

SAS Log Utilityが役立つ場面

SAS Log Utilityはどんな場面で使うのか説明します。

• SASプログラムの処理時間を短縮したいとき
• SASプログラムの入出力を解析したいとき

SASプログラムの処理時間を短縮したいとき

SAS Log Utilityを使ってSASログを解析するとボトルネックとなっている処理を特定できます。SASログを解析して、データステップ/プロシージャ単位、データセット単位、マクロ単位で処理時間を集計することができます。集計結果をExcelに出力して、処理時間順でソートすることで、ネックとなっている処理が浮かび上がってきます。

SASプログラムの入出力を解析したいとき

他の誰かが書いたSASプログラムをメンテナンスしなければならない。でも、入出力仕様書も設計書もないとしたら、SASプログラムと実際のデータを紐解くしかありません。コメントの無い長いプログラムを読み解くとしたら、大変時間の掛かる作業になります。

SAS Log Utilityは、SASログを解析して、入出力のデータセット、ファイルをCRUD表として出力します。CRUD表はデータの流れを可視化して、プログラムの理解を助けます。

マクロ単位で処理時間を集計

マクロ単位で処理時間を集計する機能を実装しました。3週間以内に1.0.4.0としてリリースします。マクロ単位で処理時間を集計するためには、options mprint又はmprintnestを付けてＳＡＳを実行してください。mprintnestはSAS9の新しいオプションです。

データの量をどうやって示すか？

ボトルネック解析で、OBS数、変数の数を追跡できるようにしようと考えています。実験的に、OBS数、変数の数をステータスバーの右下に表示してみました。数値で見ても、いまひとつピンときません。やはり、アナログでデータの量を示す工夫が必要です。StringGridの仕組みから離れて考えるべきか、良い方法を探しています。

ボトルネック解析 データ量の可視化

新しくボトルネック解析の手法をSAS Log Utilityに組み込むことを検討しています。
効率の悪いロジックは、大量のデータを転がして、雪だるまのように大きくします。結果に含めない無駄な変数、OBSは早い段階で削除しておく方が効率が良いです。

SASログには、OBS数、変数の数、データセットのページ数が含まれています。これを直感的に分かりやすく表示して、無駄にデータを読んでいるところを見つけられる仕掛けを考えています。処理の最後で、ガクッとデータ数、変数が減っているところに、性能改善の可能性があるはずです。

直感的にデータの量が分かる。例えば、色、線の太さ、図形の大きさとか。思案中です。

バグ修正 入力ファイルがシングルクォーツで囲まれているとき

SAS 8.2のログを検証していて、バグを見つけました。
入力ファイル名がシングルクォートで囲まれているときに、ファイル名が取れずに空白になっていました。

SAS8.2 -> NOTE: 100 レコードを入力ファイル 'c:\temp\foo.txt' から読み込みました。
SAS9.1.3 ->NOTE: 100 レコードを入力ファイル "c:\temp\foo.txt" から読み込みました。

次のリリースで直します。

SAS 8.2(日本語)のログを試験中

あ、特に何もしなくてもSAS8.2のログが解析が通る。どこに差があるのか探していますが、8.2の環境がないので、過去のログを手繰って比較しています。

SAS8 -> NOTE: DATA ステートメント 処理 (合計処理時間):
SAS9 -> NOTE: DATA ステートメント 処理 :

完全一致しないところが歯痒いです。

ログファイルを参照する機能

覚書きです。
次のリリースで実装したいのは、ログファイルを開いて参照できる機能です。

技術的にクリアすること。
ノートパッドで指定した行番号に移動できるか？
ノートパッド以外のエディタが使えるか？
参照用の小窓でも良い？？

マクロ単位で処理時間を集計する機能を誰が使うか？

昨日、ふと思いついたアイディアですが、誰が使うのか考えています。というのも、マクロをあまり使わずにべた書きでデータステップとプロシージャで書いているコードを良く見るからです。

私は、ある程度の処理をマクロにまとめて、複数のマクロで１つのプログラムを構成するように書いています。しかし、客先で見かけるコードは、ベタで書いているケースが多いです。果たして一般的なニーズがあるのか思案しています。

書き方、コーディングスタイルとセットでないと使えない機能になかも。まぁ、自分が使えればそれで良いのか。

次に実装したい機能 マクロ単位で処理時間集計

ぽっと、思いつきですが忘れないうちにメモしておきます。
次のリリースで実装したい機能が湧いてきました。

MPRINTと処理時間の出力を拾って、マクロ単位で処理時間、CPU時間を集計する機能です。試験的にUNIX上でスクリプトを書いて集計してみると、ボトルネック解析に有効な切り口が見出せました。

Version 1.0.3.0 公開中

Vectorで新しいバージョン 1.0.3.0 を公開しました。
右上にアクセスカウンタを設置しました。

http://www.vector.co.jp/soft/winnt/business/se476976.html

CRUD(Summary)の実装

CRUD(Summary)

Version 1.0.3.0向けに、CRUD（Summary）の機能を追加しました。CRUD（Detail)がデータステップ又は、プロシージャ単位でデータのI/Oを表示するのに対し、CRUD(Summary)はログファイル単位で、データのI/Oを表示します。

SASプログラムが複数のファイルに分かれていて、プログラム間のデータを追跡するときに役立ちます。DetailだとExcelの表が大きくなりすぎて、大まかにI/Oを把握するのが難しいので。WORKに出力されるテンポラリのデータセットは表示対象には含みません。

バグ修正

「NOTE: データセット WORK.FOO の圧縮はできません。 」というメッセージを拾って名前の無い入出力データができていました。メッセージの判定条件を直しました。

次の機能拡張を検討中

割と安定したとは思っていますが、次の機能拡張を考えています。まずは、SAS8.2のログも読めるようにするべきは、UNIX版の検証もやるべきか？

VectorでSAS Log Utility 1.0.2.0公開

新しいバージョン、リリースしました。

VectorのSAS Log Utility紹介ページ

Version 1.0.2.0で以下の機能、修正を追加しました。

• 英語版のSASログを解析
• 複数のログファイルを解析
• エクスポートの処理時間を短縮
• CRUD表のエクスポートでセルに色を付けて出力
• DELETE、CHANGEのログを捕捉

Version 1.0.2.0 再リリース中

Vectorに登録申請した後で、バグを１つ見つけて修正しました。256列を超えるCRUD表のエクスポートで、エラーが出ていました。

ついでに、CRUD表のエクスポートも、修正してさらに処理時間を短縮。これまで、OLEオートメーションで処理していた値のチェックを、StringGrideのチェックに置き換えました。これで、十数秒掛かっていた処理が、2秒程度で終わるようになりました。

Vectorのページに画面イメージを貼り付けました。1.0.2.0の公開時に見えるようになるはずです。

Version 1.0.2.0は、だいぶ使えるバージョンになったと思います。Vectorで公開されたら、お知らせします。

バグ修正と改良

今週末の修正

週末は、バグ対応とエクスポートの機能を修正しました。
これで、バージョン番号を更新してリリースを行います。

CRUD表の出力カラム数が256を超えるときのバグを修正しました。256列以降のデータは、次のシートに書き込みます。

EXCELへのエクスポートが遅いので、CSVファイル経由で渡すように書き直しました。また、CRUD表の書き込み、読み込み、更新、削除を画面と同じように色をつけました。これで見やすくなります。

次のリリース予定

バージョンは1.0.2.0
英語版のログを読めるように修正
複数のログファイルを読めるように修正
Excelへのエクスポート時間を短縮
CRUD表のエクスポートで、R/W/D/RWに色をつけました。

バグ修正

データセット単位の処理時間集計で、カウントが0回となるバグを修正しました。

OBS数が０のときに、プロシージャの入力データセット名が空白になる問題を修正しました。SASログのメッセージが、データがあるときと、無いときでは異なっていました。

複数のログファイルに対応

複数のログファイルに対応するように修正しました。これまでは、1ファイルしか解析できませんでしたが、Log Fileのタブシートからファイルを選択して並び順を指定することができます。下は、新しく追加したログファイルの指定画面です。



CRUD表には、ファイル名の列を追加しました。



Elapsed Timeのタブにもログファイルの列を追加しました。データセット単位の処理時間集計については、ファイル名で分けずに集計しています。

英語版のログ対応

SAS 9.1.3（英語版）のログを読み取れるように修正しました。英語版SASのログを取るために、一度SAS9.1.3を再インストールしましたが、IKERNEL.EXEが止まって難儀しました。



日本語版のログ

SAS Log Utilityで解析するとこんな表になります。

データセット名の変更に対応

PROC DATASETSのCHANGEステートメントのログを拾えるように修正しました。CRUD表上では、名前を変更されたデータセットは削除して、新たに作成されるように見えます。下の図では、No2 Line 47の部分がデータセット名の変更にあたります。

英語版のメッセージ対応も進行中です。Windows 9.1.3のロケールを切り替える方法を探していますが、まだ見つかっていません。UNIX版でログを取ってテストすべきか思案中。

Vectorへの登録が完了

思ったよりも素早くVectorに登録されました。次のリリースまでに何を改良するか検討します。

http://www.vector.co.jp/soft/winnt/business/se476976.html

入出力ファイル名のバグ修正

入出力ファイル名が正しく拾えていない問題を修正しました。実装で悩んだのが、ファイル参照名を実ファイル名として表示するかどうかです。入力ファイルがワイルドカードで複数のファイルから構成されるときには、 実体のファイル名が多くなって見づらいかもしれません。テキストファイルへの出力がそれほど多くないため、ファイル参照名で表示するように決定しました。



テストに使ったログを貼り付けておきます。

184 filename myfile 'c:\temp\name.txt';185186 data _null_;187
set sashelp.class;188 file myfile;189 put name;190 run;
NOTE: 出力ファイル MYFILE
: ファイル名=c:\temp\name.txt, レコードフォーマット=V,論理レコード長=256
NOTE: 19 レコードを出力ファイル
MYFILE に書き込みました。 最小レコード長は 4 です。 最大レコード長は 7 です。NOTE: データセット SASHELP.CLASS から 19
オブザベーションを読み込みました。NOTE: DATA ステートメント 処理 (合計処理時間): 処理時間 0.03 秒 CPU 時間 0.00 秒
191192 data _null_;193 set sashelp.class;194 file "c:\temp\name2.txt";195
put name;196 run;
NOTE: 出力ファイル "c:\temp\name2.txt" :
ファイル名=c:\temp\name2.txt, レコードフォーマット=V,論理レコード長=256
NOTE: 19 レコードを出力ファイル
"c:\temp\name2.txt" に書き込みました。 最小レコード長は 4 です。 最大レコード長は 7 です。NOTE: データセット
SASHELP.CLASS から 19 オブザベーションを読み込みました。NOTE: DATA ステートメント 処理 (合計処理時間): 処理時間 0.03 秒
CPU 時間 0.00 秒
197198 data x;199 infile myfile;200 input name $ ;201 run;
NOTE: 入力ファイル MYFILE : ファイル名=c:\temp\name.txt, レコードフォーマット=V,論理レコード長=256
NOTE: 19 レコードを入力ファイル MYFILE から読み込みました。 最小レコード長は 4 です。 最大レコード長は 7 です。NOTE:
データセット WORK.X は 19 オブザベーション、 1 変数です。NOTE: DATA ステートメント 処理 (合計処理時間): 処理時間 0.01 秒
CPU 時間 0.00 秒
202203 data y;204 infile "c:\temp\name2.txt";205 input name
$;206 run;
NOTE: 入力ファイル "c:\temp\name2.txt" : ファイル名=c:\temp\name2.txt,
レコードフォーマット=V,論理レコード長=256
NOTE: 19 レコードを入力ファイル "c:\temp\name2.txt" から読み込みました。
最小レコード長は 4 です。 最大レコード長は 7 です。NOTE: データセット WORK.Y は 19 オブザベーション、 1 変数です。NOTE:
DATA ステートメント 処理 (合計処理時間): 処理時間 0.01 秒 CPU 時間 0.00 秒
207208 proc datasets
lib=work nolist;209 delete x y;210 quit;
NOTE: WORK.X (memtype=DATA)
を削除します。NOTE: WORK.Y (memtype=DATA) を削除します。NOTE: PROCEDURE DATASETS 処理 (合計処理時間):
処理時間 0.01 秒 CPU 時間 0.00 秒

データセットの削除をCRUD表に追加

CRUD表と言いながらも、データセットの削除が捕捉できていませんでした。"NOTE: WORK.FOO (memtype=DATA) を削除します。"というメッセージを捉えて、CRUD表に"d"と表示するようにしました。

Vectorに登録を申請

ようやく、最初のバージョンをVectorに登録申請しました。
Vectorの審査が済んで、公開されるまで2～3週間は掛かりそうです。

SHARP 液晶TV LC-37DS5

家電製品のメモです。

SAS Log Utilityの画面イメージ(2)

データセット単位で、入出力回数、処理時間を集計する画面です。ボトルネックの解析に使います。

この画面は、データセット、ファイルの入出力を解析するものです。データセットの読み取り、書き込み、読み書き、削除を処理のステップ順に表します。ひとことで言うとCRUD表です。

SAS Log Utilityの画面イメージ

SAS Log Utilityの画面イメージを貼り付けます。これは、SASログから処理時間の情報を抽出した画面です。

バージョン情報のダイアログを追加

週末に以下の作業を行った。

バージョン情報のダイアログを追加
製品名、バージョン、著作権情報、このブログのURLを記載したダイアログを追加した。URLをクリックすると、ブログを表示するようにした。

メモリリークの対応
ReportMemoryLeaksOnShutdown := True の1行をdprファイル中に追加して、メモリリークの調査を行った。クラスの実装をやり直して、解放漏れを無くした。

ListViewのクリア
解析ボタンを2回押すと、前の結果がクリアされずに追加される問題を修正した。

多言語対応の機能をチェック
英語に対応させる機能をチェックしたが、コード中でハードコードした部分はどうやってマルチリンガルにするのか不明。要調査。

SAS Log Utilityの概要

SAS Log Utilityの概要について説明します。

概要
SAS Log Utilityは、SASプログラムが出力するログファイルを分析して、パフォーマンス診断や入出力の解析に役立つ情報を作成します。SAS Log Utilityを使うと以下のことができます。

• SASプログラムの処理時間をDATAステップで集計して、ボトルネックとなっている処理を見つけることができます。
• SASプログラムがどのようなデータを読み書きしているか、DATAステップ又はプロシージャ単位で捉えて、データの流れを理解することができます。
• MPRINTのログを捉えて、マクロ単位の処理時間を集計します。
• 複数のSASプログラムがどのようなデータを読み書きし、プログラム間でのデータの連携を可視化します。

機能

• SASログからDATAステップ/プロシージャ単位の処理時間を抽出
• SASログから処理時間を抽出し、データセット単位で処理に要した時間を集計
• SASログからDATAステップ/プロシージャの入出力を抽出し、データの生成、読み取り、更新、削除の流れを表形式に出力
• SASログからMPRINTのログを抽出し、マクロ単位の処理時間を集計
• SASログの分析結果をExcelに出力

動作環境

SAS Log Utilityの動作環境は次のとおりです。

動作環境

• 動作OS： Windows 2000/XP
• 動作機種： 汎用　
• ソフトの種類： フリーソフト又はシェアウエア
• 作者： mining

検証環境

• SAS 8.2（日本語 及び 英語）
• SAS 9.1.3 SP4(日本語 及び 英語)
• Microsoft Office Excel 2003

制約

• タグソートの入力データセットはSASログから補足できないため、分析の対象外となります。
• データセットの削除については、PROC DATASETSで削除されたデータだけが分析の対象となります。
• 分析対象のデータセットは999件を上限とします。

SAS Utilityの開発

はじめに
このブログは、SAS言語のユーティリティを開発するための日記です。

SAS言語のユーティリティとは？
これから開発しようとしているSAS言語のユーティリティとは、SASのプログラムやログを分析して、パフォーマンス解析や データセットの入出力を可視化するプログラムです。

何故、ユーティリティを作ろうと思ったのか
私がBase SASを仕事で使い始めて、数年が過ぎました。 最初は古いシンタックス、わからないマクロに手を焼きましたが、だいぶ身体が慣れてSASでできることが広がってきています。それでも、質より量をこなさなければならない仕事もあり、使えるユーティリティを作る必要が出てきました。

過去にExcelとVBAの組み合わせで簡単なものを作ってみましたが、複数の機能を1つのExcelファイルに組み込み難いという 問題がありました。欲しいのは「これ1本で簡単に使える」というツールです。

そこで新しくDelphiを学び、ネイティブのWindowsアプリケーションとしてユーティリティを開発することを決めました。Delphiを選んだ理由は、ランタイム不要で動くバイナリを生成できるからです。