FedSQL プロシジャは、SQLプロシジャとどう違うの？

データステップ100万回 SAS新手一生さんにて、「PROC SQL では LIMIT 句が使えないけどPROC FEDSQLでは使えるよ。ついでにOFFSET句も。の話」を読んで、SQLプロシジャと FedSQL ってなにが違うんだろ、と疑問に思ったので少し調べてみました。そもそも、FedSQLプロシジャの存在すら知らない（汗。　そんなこんなで、個人的に気になったところを簡単にピックアップしてみます。

FedSQLプロシジャとは？

FedSQLプロシジャは、新しいデータタイプに対応し、(SAS9.4の時点で) ANSI SQL:1999 に準拠したSQL を記載することができます。 FesSQLプロシジャが準拠している ANSI SQL:1999 ですが、それ以降の ANSI SQL はもちろん準拠していません。例えば、ANSI SQL:2003 で追加になったウィンドウ関数 partition by などは残念ながらないです。悲しい。

FedSQL の Fed は、たぶん、federal (連邦の) か federation (連邦政府) の略ですかね、、、知ってる方がいらっしゃたら教えて下さい。。。

一方、SQLプロシジャでは完全にANSIに準拠しているわけではなく、もともとのSASの動きぽいところと互換性をもたせています。ですので、OracleやらDB2を使っていた人からすると、違和感があるような記載方法や動作をしたりすることがあります。

では、FedSQLプロシジャとSQLプロシジャでどこが違うのか見ていきましょう。

null (欠損値) の扱い方 - マイナス無限

SAS忘備録さんの「SQLプロシジャ入門15：NULLの取扱い」にて記載されていますが、DATAステップも含めて、SQLプロシジャでは、欠損値はマイナス無限みたいな扱いになっています。例えば、where句で A < 10 みたいに絞り込みを実施すると、通常のSQLでは欠損値は条件の対象外ですが、SQLプロシジャやDATAステップでは欠損値も含んでしまいます。

FedSQLプロシジャに、ansimodeオプションを付けると、ANSI準拠の動きになり、where句で A < 10 みたいに絞り込みを実施したとき、欠損値を含まなくなります。デフォルトだと欠損値を含むような動きをします。

/* テストデータ */
data TEST ;
  input A ;
cards ;
1234
.
0
-100
.
run ;

title "DATAステップによる例" ;
data TEST_1 ;
  set TEST ;
  if A < 10 ;
run ;

proc print data=TEST_1 noobs ;
run ;

title "SQLプロシジャによる例" ;
proc sql ;
  select A
    from TEST
   where A < 10
 ;
quit ;

title "FedSQLプロシジャによる例 (デフォルト)" ;
proc fedsql ;
  select A
    from TEST
   where A < 10
 ;
quit ;

/* FedSQLプロシジャによる例 (ANSIMODE) */
proc fedsql ;
  select A
    from TEST
   where A < 10
 ;
quit ;

結果をみると、ansimodeオプションをつけた FedSQLプロシジャは、欠損値がなくなっていることが分かるかと思います。

私は SQL畑から、SASの世界に入ってきたのですが、初めは SASの挙動の仕方に戸惑うことが多かったです。こういう今までの世界と同じ動きができるものがひとつでもあるというのは、とても嬉しいですね。

null (欠損値) の扱い方 - イコール(数値欠損値)

SQLプロシジャやDATAステップだと、条件式に欠損値かどうかを = を使って表現できます。FedSQLだときちんと is null を使わないといけません。

/* テストデータ */
data TEST ;
  input A ;
cards ;
1234
.
0
-100
.
run ;

title "FedSQLプロシジャによる例 (デフォルト)" ;
proc fedsql ;
  select A
    from TEST
   where A = . ;
 ;
quit ;

title "FedSQLプロシジャによる例 (ANSIMODE / イコール / 0オブザベーション)" ;
proc fedsql ansimode ;
  select A
    from TEST
   where A = . ;
 ;
quit ;

title "FedSQLプロシジャによる例 (ANSIMODE / is null)" ;
proc fedsql ansimode ;
  select A
    from TEST
   where A is null ;
 ;
quit ;

2つ目のステップは、結果が 0オブザベーションになります。ちなみに、WHERE句を where A ^= . ; に置き換えても、0オブザベーションとなります。

null (欠損値) の扱い方 - イコール(文字欠損値)

文字欠損値は数値欠損値の場合とは、また動作が異なります。ansimode とそうでないときの欠損値の扱い方が肝ですね。

文字列の書き方ですが、ダブルクォーテーションを使うと構文エラーになります。代わりに、シングルクォーテーションを使います。

/* テストデータ */
data TEST_STR ;
  length A $10. ;
  A = "ABCDE" ; output ;
  A = ""      ; output ;
  A = " "     ; output ;
  A = "12345" ; output ;
run ;

title "1. FedSQLプロシジャによる例 (デフォルト / 構文エラー)" ;
proc fedsql ;
  select A
    from TEST_STR
   where A ^= ""
 ;
quit ;

title "2. FedSQLプロシジャによる例 (デフォルト / 構文OK)" ;
proc fedsql ;
  select A
    from TEST_STR
   where A ^= ''
 ;
quit ;

title "3. FedSQLプロシジャによる例 (デフォルト / 構文OK)" ;
proc fedsql ;
  select A
    from TEST_STR
   where A is not null
 ;
quit ;

title "4. FedSQLプロシジャによる例 (ANSIMODE / 構文エラー)" ;
proc fedsql ansimode ;
  select A
    from TEST_STR
   where A ^= ""
 ;
quit ;

title "5. FedSQLプロシジャによる例 (ANSIMODE / 構文OK)" ;
proc fedsql ansimode ;
  select A
    from TEST_STR
   where A ^= ''
 ;
quit ;

title "6. FedSQLプロシジャによる例 (ANSIMODE / 構文OK)" ;
proc fedsql ansimode ;
  select A
    from TEST_STR
   where A is not null
 ;
quit ;

構文エラーになるものを抜粋してみます。見慣れないログもでてきますが、ERROR:のところだけ見ると、文字通り、構文エラー、と表示されています。

 67         title "1. FedSQLプロシジャによる例 (デフォルト / 構文エラー)" ;
 68         proc fedsql ;
 NOTE: Connection string:
 NOTE: DRIVER=FEDSQL;CONOPTS= ( (DRIVER=BASE;CATALOG=WORK;SCHEMA= 
       (NAME=WORK;PRIMARYPATH={/tmp/SAS_workFB72000007FF_localhost.localdomain/SAS_work743C000007FF_localhost.localdomain})); 
       (DRIVER=BASE;CATALOG=WEBWORK;SCHEMA= (NAME=WEBWORK;PRIMARYPATH={/folders/myfolders/.sasstudio/webwork/RS2101})); 
       (DRIVER=BASE;CATALOG=SASUSER;SCHEMA= (NAME=SASUSER;PRIMARYPATH={/folders/myfolders/sasuser.v94})))
 69           select A
 70             from TEST_STR
 71            where A ^= ""
 72          ;
 ERROR: 構文エラー at or near """
 NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
 73         quit ;
 
 NOTE: エラーが発生したため、このステップの処理を中止しました。
 NOTE: PROCEDURE FEDSQL処理(合計処理時間):
       処理時間           0.02 秒
       CPU時間            0.02 秒

つぎに構文OKになったものを見てみましょう。デフォルトと ansimodeオプションをつけたときの動作が違います。ansimodeオプションが付くと、これまで欠損値扱いだったものが、欠損値扱いでは「空白」扱いになっているみたいですね。

複数の不等号の書き方 - ◯ < 変数 < ◯

私が SAS を始めたとき驚いたものの1つに、複数の不等号の書き方があります。例えば、条件文に -200 < A < 10 と記載することができるということです。他の言語だと、こういう数学ぽい書き方できないのが多いかと思います。よく入門書とか見ると、こういう書き方はできませんよ、と注意書きが書いてありますよね。それが、SASだと書くことができてびっくりしました。

さて、FedSQLプロシジャだとどうなるかというと、この書き方はできません。対して、SQLプロシジャだと書くことができます。

title "SQLプロシジャによる例" ;
proc sql ;
  select A
    from TEST
   where -200 < A < 10
 ;
quit ;

title "FedSQLプロシジャによる例 (構文エラー) " ;
proc fedsql ;
  select A
    from TEST
   where -200 < A < 10
 ;
quit ;

title "FedSQLプロシジャによる例 (構文OK)" ;
proc fedsql ansimode ;
  select A
    from TEST
   where -200 < A and A < 10
 ;
quit ;

日付、時間、日時の書き方

FedSQLならば、日付、時間、日時も SQL での標準的な書き方ができます。日付が特に "ddmmmyyyy"d や mdy関数よりも表現しやすい形で書けるので、とてもいいですね。

日付: DATE 'yyyy-mm-dd'
時間: TIME 'hh:nn:ss[.小数値]'
日時: TIMESTAMP ' yyyy-mm-dd hh:mm:ss[.小数値]'

/* テストデータ */
data TEST_DT ;
  format DT yymmdds10.
         TM time8.
         TS nldatm19.
         ;
  DT = mdy(12, 24, 2016) ;
  TM = "01:20:30"t ;
  TS = dhms(DT, 1, 20, 30) ;
run ;

proc fedsql ;
  select *
    from TEST_DT
   where date '2015-01-01' < DT
 ;
quit ;

proc fedsql ;
  select *
    from TEST_DT
   where time '1:19:20' < TM
 ;
quit ;

proc fedsql ;
  select *
    from TEST_DT
   where timestamp '2016-12-24 1:20:29' < TS
 ;
quit ;

それ以外にも...？

書き方がANSI SQLに準拠するようになったので、SAS/ACCESSなどで他のデータベースにアクセスするとき、「データベースで処理させようと思っていたのに SAS側で処理されてしまった！」、なんてことが減るかもしれませんね(未検証。

ここまで違いを見たところ、データベースでSQLをやってきた人にはとてもいいプロシジャなのではないかな、と思いました。SQL経験者のための FedSQLプロシジャから始める SAS入門、みたいなのがあっても面白いかもしれませんね。

FedSQLプロシジャで面白そうなのを見つけたら、またまとめてみようと思います。

このブログを検索

data Memorandum ; set Memory ; run ;