EXCELを読み込む方法と落とし穴 【LIBNAME EXCEL編】

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  A    B    C   D     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」という感じで参照できます。

libname TEST clear;

最後に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]」) を読み込むと、、

変数Cが日付値として読み込まれてしまいました。

A	B	C
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の仕様など不確定要素があったりなので、想定通りに読み込めているかを確認した方が良さそうです。