sasからExcelを読み込む際に、全ての変数を文字列として扱う話

親の顔くらい業務中に見た話ですが、excelからsasにデータを読み込む際に全てのデータを文字として扱いたいときの話です。

要はdbms="xlsx"を指定してExcelを読み込んだ後に、数値変数をデータステップで文字変数に変換するしかない(多分)という内容です。それで終わると寂しいのでsasでexcelを読み込む方法を以下に紹介します。

まず読み込むexcelは下の物です。変数bの11行目にxxxという文字があり、変数cには1行目に文字があります。それ以外は数字です。

sasのexcelエンジン(仮称)を使って読み込む場合

libname でもproc import でも良いのですが、"excel"を指定して読み込む方法がまずあります。libname hoge excel "piyo.xlsx" ; なり、proc import datafile = "piyo.xlsx" out = V_OUT dbms = excel ; です。

どちらも普通にexcelを読み込んでくれますが、同じ変数に数値と文字が混在している場合がややこしくなります。mixedオプションをyesにすると上から8行目までの値で変数属性が決まるので、Excel上で8行目までは数字、9行目に文字が入っている変数はsasでは数字変数となり、9行目の文字列は欠測扱いになります。

上記excelを読み込むと以下のようになります。変数bの文字が欠測になり、変数cは文字変数扱いです。

文字値の意図しない欠測を回避するためには「変数属性の決定をデータの8行目までではなく、データの最後まで読み込んでから行う」ことが必要になりますが、この変更はsasでは指定できずレジストリを変更しないといけません。sasのdocumentに以下の記載があります。「The options are located in a key of the Microsoft Windows registry.」じゃねーんだよ

 https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acpcref/n0msy4hy1so0ren1acm90iijxn8j.htm

Interaction

The 'TypeGuessRows' entry in your registry settings can affect the behavior of the MIXED= option.
The options are located in a key of the Microsoft Windows registry.

なおmixedをnoにすると全部数字変数として扱われるので、文字と数値の混在する変数の文字値は全部欠測になります。この場合だと変数bの11行目と変数cの1行目が欠測です。

意図しない欠測が出てくるのは困りものですがいったん見なかったことにして、表題の「excel上の変数を全て文字値にして取り込む」にはproc importではdbdsoptsを指定すればokです。例えば以下のように指定すると、変数が3つとも200文字の文字変数として扱われます。ただし上述した「9行目以降の欠測になった文字列」は欠測のまま文字化されています。惜しい。

proc import datafile = "hoge.xlsx" out = hogeim dbms = excel replace  ;
    sheet = "sheet1" ;
    getnames = yes ;
    mixed = yes ;
    dbdsopts = "DBTYPE=(a = 'CHAR(200)' b = 'CHAR(200)' c = 'CHAR(200)')" ;
    scantext = yes ;
run ;

libnameではdbsastypeオプションをworkに取り込む際のデータステップに記載すると同じように全て文字変数として取り込めますが、同じように欠測になった文字値はそのまま欠測です。

libname hoge excel "hoge\Book1.xlsx" mixed = yes ;
data hogex ;
    set hoge."Sheet1$"n (dbsastype=(a = char200 b = char200 c = char200) ) ;
run ;

もう一つexcelを読み込む方法として、xlsxエンジンを使う方法(仮称)があります。個人的にはこっちを推しています。

先と同じようにlibname hoge xlsx "piyo.xlsx" なり proc import datafile = "piyo.xlsx" out = hogeimsx dbms = xlsx ;なりを指定してExcelを読み込みます。

どちらの場合でもこうすると以下の画像の通り8列目以降の文字も格納され、該当の変数も文字値として扱われています。ただし全て数字格納された変数は数値変数になります。また数値変数も含めて、全ての変数を文字値として扱うためのdbdsoptsやdbsastypeオプションは使えません。このオプションはdbms=excel専用です。

ちょっと読み込むexcelを変えています。C変数11行目の長めの文字列も文字切れせずに格納されていますね。

dbdsoptsオプションが使えないので全ての変数を文字値とするには、読み込んだ後にデータステップで加工するしかないと思います。私はlibname を使用した場合はworkに格納する際のデータステップで数字変数を文字に変換しています。文字変数に変換したい変数が少ないと良いのですが、数が増えると変換漏れが出たりするので注意が必要です。importでもオプションが使えないので同じようにデータステップでの加工が必要です。

 libname hoge xlsx "hoge.xlsx" ;

data hoge2 ;
    set hoge.Sheet1 ;
    length V_A $100 ;

    V_A = cats(A) ;
    drop A ;
    rename V_A = A ;
run ;

この方法には大きな問題があって、libnameなりimportなりでexcelを読み込んだ後にデータステップで加工しているので、何かしらの問題、それこそ文字変数が意図せず欠測として扱われている等、が起きている場合に修正ができません。excel指定でexcelを読み込むよりは不具合なくデータは読み込めていますがやはり確認は必要です。

今後xlsxエンジン(仮称)でlibnameなりimportなりを使ってexcelを読み込む時点でのオプションで、excel内の全ての変数を文字値として指定できる方法が追加されることを祈っています。ddeはあまり使いたくないので…