EXCELファイルをRでxptファイルに変換し、SASで回収する話

 BASE SASではproc importのdbmsにxlsxあたりを指定できないという話があります。excelを読み込みたくばSAS/ACCESS Interface to PC Filesを導入しろとのSAS社からの思し召しなのですが、そう言われるとどうにかならんのかと考えてしまいますね。こんな取り組みは弊社の業務では1mmも使わないので無駄なのですが、気に食わないものはしょうがない。最近はpythonがどうだと大盛り上がりですが、今回はRでexcelファイルをxptに変換して、sasでそれを読み込むことを目指します。sas7bdatは仕様が公開されていないらしいので、どうしてもsas以外を使うとXPTファイルにしかならないのは考え物です…案外私が知らないだけでどうにかなるのかもしれませんが。

まずはR恒例のパッケージインストールから。以下のコードを実行すると、パッケージがパソコンに入っていると普通にロードを、なければダウンロードします。今回はdplyrとtidyverseを使います。libsにほしいパッケージを指定すれば、なにもこの二つに限らず実行できます。

libs <- c("dplyr","haven","readxl")

requireLibs <- function(libs) {

  for(lib in libs){

    if(!require(lib, character.only = T)){

      install.packages(lib)

      require(lib, character.only = T)

    }

  }

}

requireLibs(libs)

まずはexcelを読み込みます。Rはいろんなパッケージがあって、同じ処理でもいろんな関数があって困るのですが、私はread_xlsxを使っています。よく似た名前のread.xlsxもあるので難しいですね。read_xlsxは標準だと変数の型推定が guess_max = min(1000, n_max),となっているので1000行まで判定してくれます。データが多そうなら適宜この引数を変えましょう。 何もしなくても1000まで判定してくれるのえらいですね。

inpath <- "piyopiyo/book1.xlsx"

infile <- read_xlsx(path = inpath, sheet = "Sheet1")

いざ中身を見ると以下の内容です。全部数値のAは数字に、文字を含んでいるBとCは文字値になっていて、Cの11行目の長い文字列も切れずに読み込めています。データはtibble型になっていますがデータフレームです。tidyverseではこっちをよく使っているようですが。

head(infile, 11)

# A tibble: 11 × 3

       a b     c         

   <dbl> <chr> <chr>     

 1     1 1     xxxx      

 2     2 2     2         

 3     3 3     3         

 4     4 4     4         

 5     5 5     5         

 6     6 6     6         

 7     7 7     7         

 8     8 8     8         

 9     9 9     9         

10    10 10    10        

11    11 xxxx  xxxxxxxxxx

このinfileをそのままxptにしてもよいのですが、すべての変数を文字値として扱いたい時を想定して一処理追加します。

chr2_infile <- infile %>% mutate_if(. , .predicate=is.numeric, .funs= ~as.character(.) )

上記でinfileの数字変数、今回でいうところのAを文字に変換して、chr2_infileという名前に変えています。もう少し解説すると、mutate関数でデータフレームの列単位で一括処理ができますが、今回はmutate_ifにすることで、数字変数に対して一括で文字値化する処理をしています。.predicateに処理をかける変数の条件を、.funsに実際に行う処理を指定します。変数単位で指定して処理を書くのはSASではあまりない考えで好きです。まだ私もピンと来ていないところが多々ありますが…

作ったchr2_infileの中身を見てみると、Aが無事chrに変換されているのがわかります。

 head(chr2_infile, 11)

# A tibble: 11 × 3

   a     b     c         

   <chr> <chr> <chr>     

 1 1     1     xxxx      

 2 2     2     2         

 3 3     3     3         

 4 4     4     4         

 5 5     5     5         

 6 6     6     6         

 7 7     7     7         

 8 8     8     8         

 9 9     9     9         

10 10    10    10        

11 11    xxxx  xxxxxxxxxx

最後に作ったchr2_infileをxptファイルに変換します。version引数は標準だと8なのですがなぜか8だと作ったxptをsasで読み込めません。sas側がerror出します。仕方がないのでversionは5とします。この引数は5か8しか指定できません。

お察しの通りxpt version 5で作成しますので、変数名が長すぎたりするとうまく作れないと思います。試してませんけど…

write_xpt(chr2_infile, "piyopiyo/book1.xpt", version = 5 )

上記を実行すると第二引数に指定したパスとファイル名でxptファイルが出力されますので、sasから回収に向かいましょう。これは私のいつもの書き方なのですが、Libnameステートメントにxptファイルを指定して、それをworkにコピーしています。コピーしたら元のlibnameは要らないのでクリアします。

libname INXPT xport "piyopiyo\book1.xpt" ;

proc copy in=INXPT out = work ; 

run ;

libname INXPT clear ;

workにコピーしたbook1データセットを見に行くと、無事以下のように格納されています。文字長に余裕とかは無いので、Aはlengthが2です。この辺はRから作ったxptファイルなのでしょうがないですね。そもそも固定長じゃないしね。

ブログの途中であったxptのversionが5じゃないとSAS側がerror出すというのは結構今一ポイントです。一応githubにxptをproc copeyではなくて自動マクロの%xpt2locでworkに移せばversion 8のxptでも読み込めるよとあるのですが、実際にやってみると実行時にerrorは出ませんが0obsのxptとして読み込まれてしまうので、書き出し時のxptのverisonは5を指定する必要があります。…今後の修正と更新に期待です。xptのversion5って変数名8文字とかのとんでもなく厳しい縛りがあるのであまり使いたくないんですよね…

それはそうとてBASE SASしかないとEXCELファイルを読み込むのにずいぶん難儀すると思いますが、Rを使ったこの方法で何とかBASE SASしかない環境でもEXCELが読み込めるようになるとよいのですが。xptのversionだけはどうにかならんのか。今時5て。自分の業務には1mmも関係ないですけど…

ちょっと記事内にとっ散らかったので最後にR側のコードをまとめておきます。

#パッケージインポート

libs <- c("dplyr","haven","readxl")

requireLibs <- function(libs) {

  for(lib in libs){

    if(!require(lib, character.only = T)){

      install.packages(lib)

      require(lib, character.only = T)

    }

  }

}

requireLibs(libs)

#excelをRに読み込み

inpath <- "piyopiyo/book1.xlsx"

infile <- read_xlsx(path = inpath, sheet = "Sheet1")

#数字変数を文字変数に変換

chr2_infile <- infile %>% mutate_if(. , .predicate=is.numeric, .funs= ~as.character(.) )

#xptに変換

write_xpt(chr2_infile, "piyopiyo/book1.xpt", version = 5 )