SQLビューは同じ変数名が複数あってもエラーにならない

SQLビューの落とし穴です。以下をご覧ください。

proc sql;    create view myview as    select  name,               111 as var1,               222 as var1    from  sashelp.class; quit; proc print data=myview noobs; run;

なにこれ、VAR1が2つ存在しちゃってる、エラーにもなってない。。

実はCREATE VIEWでは、同じ名前の変数がいくつも存在する状態を作り出すことが出来てしまうんです。

では、先ほどのSQLビューに対して、VAR1を選択してPROC PRINTすると、、

proc print data=myview noobs;    var var1; run;

1個目のVAR1がプリントされました。2個目はどうした。



私自身、この挙動を知らずにミスったことがあります。
その時の状況として、変数が数十個あるデータセットに対して、

select  *, x+y as var1

みたいなselect文を書いたSQLビューを作りました。ですが元々データセットにVAR1っていう変数が存在していて、それを知らずにSQLビューの中でもVAR1っていう変数を作ってしまっていました。

このSQLビュー内で作ったVAR1に対して、

proc means data=○○;
   var var1;
run;

みたいな事をやろうとしたところ、元々データセットに存在しているVAR1に対して集計が行なわれてしまい、結果がおかしい！？ってなりました。



See also...
SQLビューの作成と落とし穴①

SQLビューの作成と落とし穴②

「SQLビューの作成と落とし穴①」 の続き。



落とし穴①

---

以下は「TEST.PITFALL」というSQLビューを作成しています。

data DT1;    A=1; output;    A=2; output; run; libname TEST "C:\Myfiles"; proc sql;    create view TEST.PITFALL as    select *    from DT1; quit;

続いて、このSQLビューをPROC PRINTで出力してみます。

proc print data=TEST.PITFALL; run; ログ ERROR: ファイルTEST.DT1.DATAは存在しません。 ERROR:  直接的または間接的に参照されたデータセット、ビューの 1 つがオープンできなかったため、SQL ビュー TEST.PITFALL を処理できませんでした

ERRORが出ました。実はSQLビューの性質として、
FROM句のデータセットは、デフォルトでSQLビューと同じライブラリを参照しにいきます。

つまり、SQLビューがTESTライブラリにあるので、「from DT1」は「from TEST.DT1」と置き換えて実行されます。


今回の例では、FROM句に WORK.DT1 を適用したいので、以下のように明示的に指定する必要があります。

proc sql;   create view TEST.PITFALL as   select *   from WORK.DT1; quit;

落とし穴②

---

SQLビュー参照時にシステムオプションが2回適用されてしまうケースがあります。

例えば、システムオプション「FIRSTOBS=」や「OBS=」がそれにあたります。

まずは、システムオプションの「FIRSTOBS=」・・・ではなくデータセットオプションの「FIRSTOBS=」を使った場合の例。

proc sql;    create view MYCLASS as    select *    from SASHELP.CLASS; quit; proc print data=MYCLASS (firstobs=10) noobs; run;

データセットオプションで「firstobs=10」としてるので、PROC PRINTでの出力時にオブザベーション=10から処理が開始されています。これは想定通りです。

では次、システムオプションの「FIRSTOBS=」を使った場合。

proc sql;    create view MYCLASS as    select *    from SASHELP.CLASS; quit; options firstobs=10; proc print data=MYCLASS noobs; run; options firstobs=1;

「options firstobs=10;」で以降の処理の開始をオブザベーション=10からにしています。

• まずはPROC PRINTでSQLビューを参照した際にクエリ（SELECT文）が動くので、その時にオブザベーション=10から処理が行われます
• そのクエリの結果に対してPROC PRINTを実行する際にも、オブザベーション=10から処理が行われます

つまり、FIRSTOBS=システムオプションが2回適用されちゃったわけですね。。

（話それますが、FIRSTOBS=オプションは以下の落とし穴もあって、取り扱い注意）

「FIRSTOBSオプションの落とし穴」

その他の落とし穴

---

• その他にも「SQLビューはデータセットと違い実態がない」という事に起因して、一部のデータセットオプションが効かない事もあるので、動作について検証が必要です

• SQLビュー内に記述した「&X」みたいなマクロ変数は展開された上で保存されます

• 他の落とし穴についても別記事で書きました

「SQLビューは同じ変数名が複数あってもエラーにならない」

SQLビューの作成と落とし穴①

まずはSQLビューについて説明して、次の記事で落とし穴を紹介（記事の最後にリンク貼ってます）。

ミスを起こしやすい仕様があるので落とし穴の記事も必ず参照下さい！

以下をご覧ください

proc sql;    create view MYVIEW as    select NAME, AGE    from SASHELP.CLASS; quit; proc print data=MYVIEW noobs; run;

SQLプロシジャの CREATE VIEW で作成した MYVIEW は一見データセットに見えますが、「SQLビュー」というものです。
「SQLビュー」には、データセットではなく、定義したSELECT文が保存されます。
SQLビューを開いたり、参照すると、定義したSELECT文が実行されて結果のデータが返ってくる仕組みになっています。



構文

PROC SQL;           CREATE VIEW ビュー名 AS  SELECT文    QUIT;

ビューの定義を見たい場合は DESCRIBE VIEW を使います。

proc sql;    describe view MYVIEW; quit; ログ NOTE: SQLビューは WORK.MYVIEW は次のように定義されています :         select NAME, AGE           from SASHELP.CLASS;

ちなみにビューを削除したい場合は DROP VIEW を使います。

proc sql;    drop view MYVIEW; quit;

SQLビューのメリット

---

便利なのが
たとえば、以下のようにデータセット MYLIB.DT1 を参照している MYLIB.VW というビューを作ったとします。

proc sql;    create view MYLIB.VW as    select A+B as C    from MYLIB.DT1; quit;

そしてこのビューを使って翌日 PROC MEANS を実行したいとします。

proc means data=MYLIB.VW;    var C; run;

しかし翌日、ビューが参照している MYLIB.DT1 に値の変更が発生したとします。
この場合、再度ビューを作り直す必要はありません。

なぜなら、以下のように

proc means data=MYLIB.VW;    var C; run;

ビューを参照するだけで、内部で最新の MYLIB.DT1 に対してSELECT文が実行され、その結果に対してMEANSが実行されるからです。

またメリットとして、ビューの中身は定義なのでファイルサイズは小さいです。
ただし、プログラム中に何回も同じ中身のビューを参照するような状況では、参照するたびに内部でSELECT文が実行される事になるので、処理効率が悪くなります。

次回は「落とし穴編」