SQLプロシジャのちょっとしたTips2つ

FEEDBACKオプション

proc sql ;
  create table temp1 as
  select t1.*,
         t2.product_name,
         t2.product_price
  from sales as t1 left join master as t2
  on t1.product_code=t2.product_code
quit ;

のようにSELECT句で*を使用する場合に、

proc sql feedback ;
  create table temp1 as
  select t1.*,
     ～

と書くと、ログに*の内容が出力される

DESCRIBEステートメント

■Viewの場合
例えば上記と同じようなSQLプロシジャでViewを定義してある場合、

proc sql ;
  create view temp1_vw as
  select t1.*,
         t2.product_name,
         t2.product_price
  from sales as t1 left join master as t2
  on t1.product_code=t2.product_code
quit ;

以下のようなSQLプロシジャを実行すると、

proc sql ;
  describe view temp1_vw ;
quit ;

ログにViewの定義情報が出力される（クエリ内容）

■Tableの場合
同じようにSQLプロシジャでTableを作成してある場合、

proc sql ;
  create table temp1_tb as
  select t1.*,
         t2.product_name,
         t2.product_price
  from sales as t1 left join master as t2
  on t1.product_code=t2.product_code
quit ;

以下のようなSQLプロシジャを実行すると、

proc sql ;
  describe table temp1_tb ;
quit ;

ログにTableの定義情報が出力される（ラベル、フォーマット、レングス等）

便利な条件分岐処理 CASE式を使ってみよう！

条件分岐処理は、SASに限らず様々なプログラミング言語で不可欠なものです。

SASのDATAステップ処理においては、おなじみの

・IF　条件式　THEN　処理　;

・IF　条件式　THEN　処理１　;

ELSE　処理２　;

・IF　条件式１　THEN　処理１　;

ELSE IF　条件式２　THEN　処理２　;

や、SELECTステートメントを使用した、

・SELECT（変数名）　;

WHEN（条件１）処理１　;

WHEN（条件２）処理２　;

OTHERWISE　;

END　;

・SELECT　；

WHEN（条件式１）処理１　;

WHEN（条件式２）処理２　;

OTHERWISE　;

END ;

などの書き方があります。

では、SQLプロシジャでの条件分岐処理はどのように記述すればいいでしょうか。

例えば、以下のようなSASデータセットがあるとき、



変数VAR2の値が偶数のときは、変数VAR3に"偶数"を、

変数VAR2の値が奇数のときは、変数VAR3に"奇数"を格納する処理をしてみます。

proc sql ;

create table temp2 as

select var1,

var2,

case mod(var2,2)

when 0 then "偶数"

else "奇数"

end as var3

from temp ;

quit ;

構文は以下の通りです。

CASE 変数名　WHEN 変数の値１　THEN 返す値１　WHEN 変数の値２　THEN 返す値２　～　ELSE 返す値　END

CASEの後に変数名と書きましたが、上記プログラムのように関数を直接記述することも可能です。

その場合は、変数の値と書いた箇所を関数の戻り値と解釈して下さい。

また、CASE式は「IF　～　THEN　～　; ELSE IF　～　THEN　～　; ELSE　～　;」のように評価していきます。

よって、真と評価された時点でCASE式を抜けます。また、全て偽と評価されればELSE以降の処理が行われます。

記述方法は少し異なりますが、以下のような書き方も可能です。

proc sql ;

create table temp3 as

select var1,

var2,

case

when mod(var2,2)=0 then "偶数"

else "奇数"

end as var3

from temp ;

quit ;

最後にそれぞれの実行ログと出力結果を掲載しておきます。

[google]

2つのテーブルの片方にだけ存在するキーを抽出する方法

SASでは、当然のことながらテーブルの結合は欠かせません。

DATAステップで行うのであれば、

縦結合の場合はSETステートメント、横結合の場合はMERGEステートメントが一般的です。

SQL（SQLプロシジャ）でもそのほとんどの処理（あるいはそれ以上の処理）を

記述することができます。

今回は、記事のタイトルの通り、

「2つのテーブルの片方にだけ存在するキーを抽出する」方法を掲載します。

以下のようなSASデータセット『temp1』と『temp2』を使います。

『temp1.sas7bdat』



『temp2.sas7bdat』



ここでは、『temp1』にだけ存在するキーを抽出してみます。

つまり、変数KEYが5のレコードのみを抽出します。

まずは、DATAステップのMERGEステートメントでの実装例です。

data temp3_mrg ;

merge temp1(in=in1) temp2(in=in2) ;

by key ;

if in1 and ^in2 ;

run ;

これは基本ですね。

IN=オプションで、「in1」「in2」という変数名をMERGEステートメントで指定して、

BYステートメントでは結合キー（ここでは変数KEY）を指定します。

サブセット化IFステートメントでは、結合の際の抽出条件を指定します。

『temp1』にだけ存在するレコードを抽出するので、"in1 and ^in2"という条件文になります。

出力結果は以下の通りです。



では、これと同じ結果をSQLプロシジャで出してみます。

まずは、サブクエリを使用するパターンです。

proc sql ;

create table temp3_sql1 as

select temp1.key from temp1

where not exists

(select * from temp2

where temp1.key = temp2.key

)

;

quit ;

NOT EXISTSとサブクエリを併用します。

サブクエリでは、変数KEYでマッチする『temp2』のレコードを抽出。

サブクエリの結果に対してNOT EXISTSな『temp1』のレコードを抽出。

結果、『temp1』にだけ存在するレコードが抽出されます。

次は、サブクエリを使用しない方法です。

LEFT JOIN（左外部結合）とWHERE句の組み合わせで可能になります。

proc sql ;

create table temp3_sql2 as

select temp1.key

from temp1 left join temp2

on temp1.key = temp2.key

where temp2.key = .

;

quit ;

左外部結合は、左側のテーブル（ここでは『temp1』）を優先して全レコードを出力します。

それだけでは、『temp1』のレコードが全て抽出されるだけなので、

抽出条件をWHERE句に記述します。

ここでは、『temp1』にだけ存在する変数KEYを出力したいので、

"temp2.key = .(←欠損値)"という抽出条件を追加します。

なぜ、このような条件文になるかというと、

まず、変数KEYが1～3のときは、『temp2』にも同じ値が存在するので条件文を満たしません。

次に、変数KEYが5のときは、『temp2』に同じ値が存在しないので『temp2』の変数KEYは欠損値になります。

（ちなみに、『temp2』だけに存在する値（ここでは4）は、結合方法が左外部結合なので評価されません。）

参考までに、両方の実行ログと出力結果を載せておきます。

[google]

COUNT関数の注意点

SQLのCOUNT関数は、クエリ結果の件数を返します。

非常に便利で手軽に使える関数ですが、若干注意が必要です。

まず、以下のようなプログラムを実行してみます。

data temp ;

do i=1 to 10 ;

output ;

end ;

run ;

proc sql noprint ;

select count(i) into: cnt

from temp

;

quit ;

%put cnt ***** &cnt ***** ;

結果は下記のとおりです。



なんのことはないですね。10が返ってきています。

では、次のプログラムを実行するとどうでしょうか？

data temp ;

do i=1 to 10 ;

if mod(i,2) then j=i ;

else j=. ;

output ;

end ;

run ;

proc sql noprint ;

select count(j) into: cnt

from temp

;

quit ;

%put cnt ***** &cnt ***** ;

MOD関数を使って、変数iが奇数のときは変数jに変数iの値を、

変数iが偶数のときは変数jに欠損値を格納した場合の、変数jの件数を求めるクエリです。

結果は下記のとおりです。



10ではなく5が返ってきました。

この結果を見れば分かると思いますが、

COUNT関数は、指定した引数が欠損値の場合は件数をカウントしません。

もちろん、有効な値が入っている件数を求める目的であれば問題ありませんが、

有効な値・欠損値を問わずに件数を求める場合には期待する結果が得られません。

欠損値を含んでいるか否かが分からない場合は、

下記のように条件を加えてあげるといいでしょう。

proc sql noprint ;

select count(case j when . then 0 else 1 end) into: cnt

from temp

;

quit ;

%put cnt ***** &cnt ***** ;

[google]