Windows環境での機能で、SAS/ACCESSのライセンスも必要ですが、LIBNAMEでEXCELを読み込めます。
えぐい落とし穴もあるので最後までご覧ください。
以下のEXCELファイルを読み込みたいとします。
C:\test.xlsx
データ部分のセル書式は「A列: 標準」「B列: 標準」「C列: 日付」「D列: 時刻」としています。
例
*** 正しく読み込めないので注意 ******;
* libnameで、EXCELファイルを参照する ;
libname TEST excel "C:\test.xlsx"
header = yes
dbmax_text = 8000
scan_timetype = yes
mixed = yes ;
* SET ;
data OUT1;
set TEST."Sheet1$"n;
run;
* EXCELファイルを解放 ;
libname TEST clear;
OUT1
|
1
|
1
|
.
|
.
|
2
|
1
|
10JAN2012
|
10:20:00
|
3
|
1
|
.
|
.
|
4
|
1
|
.
|
.
|
5
|
|
.
|
.
|
6
|
|
20OCT2012
|
.
|
7
|
1
|
.
|
.
|
8
|
1
|
.
|
.
|
9
|
|
.
|
.
|
黄色で示した部分は「aaa」という文字が読み込まれるはずだけど、欠損値になってしまう事に注目。 |
解説
libname TEST excel "C:\test.xlsx"
header = yes
dbmax_text = 8000
scan_timetype = yes
mixed = yes ;
|
LIBNAMEでEXCELファイルを参照出来るようにしています。
以下が構文（オプションは一部抜粋）
libname 参照名 excel "EXCELファイルのフルパス"
header = yes | no … 読み込み範囲の最初の行を変数名として読み込むか
dbmax_text = 数値 … 文字変数の最大LENGTHを指定
scan_timetype = yes | no … 項目のデータ型が時刻かどうかスキャンして取り込むか
mixed =
yes | no … 1つの項目に数値と文字が混在してたら、文字変数として取り込むか
;
data OUT1;
set TEST."Sheet1$"n;
run;
|
EXCELの中身を、「参照名."EXCELシート名$"n」という感じで参照できます。
最後にEXCELファイルを解放。
落とし穴（環境により動作は異なる）
実は、LIBNAME EXCELでの読み込みでは、EXCELのデータ部分の先頭8行だけを調べて、データ型などの属性が決定されているようです。（レジストリの設定とか、環境によっては異なる可能性あり）
この「先頭8行ルール」により以下①～③の読み込み失敗例が考えられます。
① mixed=yesのオプションをつけると、数値と文字が混在してる項目を、文字変数にしてくれるのですが、、、
「先頭8行ルール」により、先頭8行に数値しかない場合、数値変数と判断されます。
そこで、もし9行目以降に文字があると、そこが取り込めず、欠損値になってしまいます。
黄色セルが、欠損値になってしまうやつ。
② 「先頭8行ルール」により、以下のように先頭8行が全て255バイト以内の文字で、9行目以降に256バイト以上の文字がある場合、256バイト以降の文字が切れたり、「?」とか変な文字になる。
黄色セルが、変になるやつ。
③ 「先頭8行ルール」により、以下のように先頭8行の書式が「標準」や「数値」で数値のみが入力されていると数値変数と判断されて、9行目以降に書式が「時刻」や「日付」で時刻（または日付)が入力されていると、9行目以降の当該データはEXCELのシリアル値で読み込まれてしまいます。
「10：20」の場合は「0.430555・・・」というシリアル値で読み込まれる。
ここまでが「先頭8行ルール」による落とし穴で、他にも以下のような落とし穴がある。
④ これもかなり特定の条件ですが、以下のように数値と256バイト以上の文字のみで構成されてるデータで、書式が「標準」や「数値」になってる場合、数値変数と判断されて、文字はすべて欠損値になってしまう。
黄色セルが、欠損値になってしまうやつ。
⑤ オプションの設定によって挙動が変わりますが、今回紹介したプログラムの設定のまま以下のEXCEL (データ部分の書式は「A列: 日付」「B列: 時刻」「C列: ユーザー定義[yyyy/m/d h:mm]」) を読み込むと、、
|
01JAN2012 | 12:28:00 | 01FEB2012 |
02JAN2012 | 13:00:00 | 02FEB2012 |
一応、以下の青文字で示した「SASDATEFMT=」というオプションに、対応させたいフォーマットを指定すると日時として読み込めるようです。
libname TEST excel "C:\test.xlsx"
header = yes
dbmax_text = 8000
scan_timetype = yes
mixed = yes ;
data OUT1;
set TEST."Sheet1$"n (sasdatefmt=(C=datetime19.));
run;
|
⑥ データステップ等でLIBNAME EXCELのファイルにアクセスする際、WHEREステートメントは使わない方が良さそう。
というのも、変数に欠損値があるとWHEREステートメントでうまくオブザベーションが抽出できない場合がありました。SAS社のリファレンスからはこの辺の説明が見つけられなかったので、環境によるものかも不明です。
data OUT1;
set TEST."Sheet1$"n;
where B^=1; /* ← NG */
run;
|
以上、わたしが気づいた範囲のえぐい落とし穴でした。
実は根本的な解決法が無いのが現状です。
一応自分なりに、こうするしかないかなぁー。。という方法はあるので、記事としてまとめられたら紹介したいと思います。
追記
いろいろ調べた結果、次回紹介予定の方法で正しく動く確証がもてなかったので、他にいい方法がないか、引き続き調査したいと思います。
この方法を使う際は、動作環境によってうまく動かないことがあったり、EXCELの仕様など不確定要素があったりなので、想定通りに読み込めているかを確認した方が良さそうです。
