ラベル [▼ SQLプロシジャ入門 の投稿を表示しています。 すべての投稿を表示
ラベル [▼ SQLプロシジャ入門 の投稿を表示しています。 すべての投稿を表示

2015年4月22日水曜日

SQLプロシジャ入門15：NULLの取扱い




SASのSQLプロシジャと、他のデータベースで用いられるSQLとで大きく異なるのが、NULLの取り扱いです。

同じデータに対して、SASのSQLプロシジャで実行した結果と、MSAccessのSQLで実行した結果を比較してみます。


SASのSQLプロシジャの場合
*** サンプルデータ作成 *************;
data DT1;
input A @@;
cards;
1 2 . 3
;

A 
  1 
  2  
  .  
  3
*** A < 2 のレコードを抽出 **********;
proc sql;
   select A
   from DT1
   where A < 2;
quit;

A 
  1 
  .  

*** Aが1以外のレコードを抽出 **********;
proc sql;
   select A
   from DT1
   where A ^= 1;
quit;

A 
  2  
  .  
  3


MSAccessのSQLの場合
*** サンプルデータ ******************;

*** A < 2 のレコードを抽出 ************;


結果

*** Aが1以外のレコードを抽出 ***********;


結果


比較してみると、SASでは結果にNULLのレコードが含まれているのに対し、MSAccessの結果にはNULLが含まれていません。


この違いの原因は、聞きかじり程度の知識なので、あまりあてにならないかもですが、

データベースでのNULLは「未知」とか「適用不能」といった意味合いがあるようです。
この考えからすると、今回の例のような「2より小さい」とか「1以外である」といった事を、NULLに対して語ることができないので、抽出結果からAがNULLのレコードが出てこなかったわけです。


一方、SASのSQLプロシジャでは、SAS内での互換性を保つために、上記のようなNULLの特別扱いはしていません (データステップ等での欠損値の取り扱いと一緒)

ただし、SAS/ACCESSの機能を使って、DBMS等の外部データにアクセスする場合は、NULLの扱いが異なるので注意！
例えば、LIBNAMEステートメントを使って外部データにアクセスする場合や、パススルー機能によって外部データに送信するクエリとかでは、NULLの取り扱いが異なります
（「外部データに対応するエンジン」や「記述方法」などの要素によってそれぞれNULLの挙動が異なる）

SASやDBMS等のNULLの取り扱いとも違う場合があって、私もよく分かりません。。というわけで、ご注意ください。



ちなみに、SASとは関係ない話になりますが、このNULLに関するお話が、SASYAMAさんに教えて頂いた「達人に学ぶSQL徹底指南書」というのに詳しく書かれていて、読んでて面白かったのでおススメしたいです。↓↓


以上、今回でSQLプロシジャ入門は終わり（の予定）です。

１４．データセットを縦結合する【UNION】
１５．NULLの扱いに関する注意点

0 件のコメント:
ラベル:

2015年1月19日月曜日

SQLプロシジャ入門14：データセットを縦結合する【UNION】




SQLプロシジャで複数のデータセットを縦結合する方法の紹介です。



サンプルデータ
data DT1;
input A$ B$ ;
cards;
001 aa
002 bb
002 bb
;

data DT2;
input A$ C$;
cards;
002 bb
003 cc
;

DT1
A 
 B 
  001 
  aa   
  002  
  bb  
  002
  bb  

DT2
 A 
C
  002  
  bb   
  003
  cc  



① 「UNION」 と 「UNUION ALL」
proc sql;
   create table DT3 as
   select * from DT1
      union
   select * from DT2;
quit;

DT3
A 
 B 
  001 
  aa   
  002  
  bb  
  003
  cc  
基本構文
SELECT文  union  SELECT文

解説
・SELECT文の結果を縦に結合します。

・変数名ではなく変数の順番で結合されます。
（今回の例では、DT1の変数BとDT2の変数Cは同じ2列目にある変数なので、無理矢理BとCを縦にくっつけちゃいます。）

・出力データで値が重複してるレコードは、重複分が削除されます。
（たとえば、
DT1の 「A="002" and B="bb"」 と、
DT2の 「A="002" and C="bb"」 の3レコードは重複してるので、1レコードだけ残してあとは削除されます。）

重複を削除したくない場合は、「union all」と指定すればok。




② 「UNION CORR」 と 「UNUION CORR ALL」
proc sql;
   create table DT4 as
   select * from DT1
      union corr
   select * from DT2;
quit;

DT4
A 
  001 
  002  
  003
基本構文
SELECT文  union corr  SELECT文

解説
・SELECT文の結果を縦に結合します。

・結合時に共通する変数名のみを残します。
（今回の例では、DT1とDT2で共通する変数名はAのみなので、これだけ残る。）

・出力データで値が重複してるレコードは、重複分が削除されます。
（たとえば、
DT1とDT2の 「A="002"」 の3レコードは重複なので、1レコードだけ残してあとは削除されます。）

重複を削除したくない場合は、「union corr all」と指定すればok。




③ 「OUTER UNION CORR」
proc sql;
   create table DT5 as
   select * from DT1
      outer union corr
   select * from DT2;
quit;

DT5
  A  
  B 
  C  
  001 
  aa  
  002
  bb 

  002
  bb

  002
  
  bb  
  003

  cc
基本構文
SELECT文  outer union corr  SELECT文

解説
・SELECT文の結果を縦に結合します。
・データセット間で共通する変数名同士を結合し、片方にしかない変数も残してくれてます。
・重複レコードも削除されません




１４．データセットを縦結合する【UNION】
１５．NULLの扱いに関する注意点

2 件のコメント:
ラベル: , ,

2014年12月2日火曜日

SQLプロシジャ入門13：データセットを横結合する【FULL JOIN】




横結合時に両方のデータセットの全てのレコードを残す方法を紹介。



サンプルデータ
data DT1;
  A=1; B="AA"; output;
  A=2; B="BB"; output;
run;

data DT2;
  A=2; C=10; output;
  A=3; C=20; output;
run;

DT1
 A 
B
  1  
  AA   
  2
  BB  

DT2
 A 
C
  2  
  10   
  3
  20  




 FULL JOIN
proc sql;
   create table  DT3 as
   select    coalesce( DT1.A, DT2.A ) as A ,
                B ,
                C
   from      DT1  full join  DT2  on  DT1.A = DT2.A ;
quit;

  A  
 B 
  C  
  1
  AA 
 .
  2
  BB 
 10
  3
   
 20

 基本構文
  from  データセット1  full join  データセット2  on  結合条件


解説
① 結合時、2つのデータセットの全てのレコードを残します。
 （結合条件に合うレコードだけでなく、片方のデータセットにしかないレコードも残す）


② 結合する2つのデータセットで同じ変数名を持っている場合、「データセット名.変数名」と書きます。
（今回の例では、DT1とDT2で同じ変数名Aを持っているので、どっちのAを使うのか明確にするため、「DT1.A」とか「DT2.A」と書いています）


③ SELECTでcoalesceという関数を使ってます。
この関数は、指定した引数の値を順番に見ていって、最初の非欠損値の値を返します。

つまりSELECTの「coalesce( DT1.A, DT2.A )」は、
・DT1にしか存在しないレコードだったら、DT1.Aの値を持ってくる
・DT2にしか存在しないレコードだったら、DT2.Aの値を持ってくる

という事をやっています。



１３．データセットを横結合する【FULL JOIN】

0 件のコメント:
ラベル: , ,

2014年11月19日水曜日

SQLプロシジャ入門12：データセットを横結合する【LEFT,RIGHT JOIN】




今回は、横結合時に一方のデータセットに存在するレコードだけを残す方法を紹介。
とりあえずサンプルを見てみましょう。



サンプルデータ
data DT1;
  A=1; B="AA"; output;
  A=2; B="BB"; output;
run;

data DT2;
  A=2; C=10; output;
  A=3; C=20; output;
run;

DT1
 A 
B
  1  
  AA   
  2
  BB  

DT2
 A 
C
  2  
  10   
  3
  20  



①LEFT JOIN
proc sql;
   create table  DT3 as
   select    DT1.A , B , C
   from      DT1  left join  DT2  on  DT1.A = DT2.A ;
quit;


データセットDT3
  A  
 B 
  C  
  1
  AA 
 .
  2
  BB 
 10


基本構文
 from データセット1  left join データセット2  on  結合条件


解説
・FROMで結合する時、左側に書いたデータセットDT1のレコードだけ残す。
・DT1とDT2で同じ変数名Aを持ってるので、どっちのAを使うのか明確にするため、「DT1.A」とか「DT2.A」と書いてあげる必要がある。




②RIGHT JOIN
proc sql;
   create table  DT4 as
   select    DT2.A , B , C
   from      DT1  right join  DT2  on  DT1.A = DT2.A ;
quit;
データセットDT4
  A  
 B 
  C  
  2
  BB 
 10
  3
   
 20
基本構文
 from  データセット1  right join データセット2  on  結合条件
解説
・FROMで結合する時、右側に書いたデータセットDT2のレコードだけ残す。




１２．データセットを横結合する【LEFT,RIGHT JOIN】


0 件のコメント:
ラベル: , , ,

2014年11月13日木曜日

SQLプロシジャ入門11：データセットを横結合する【INNER JOIN】




SQLプロシジャで横結合する方法を紹介していきます。
まずは、2つのデータセットを横結合して、指定した結合条件に合致するレコードのみを残す方法。



サンプルデータ
data DT1;
  A=1; B="AA"; output;
  A=2; B="BB"; output;
run;

data DT2;
  A=2; C=10; output;
  A=3; C=20; output;
run;

DT1
 A 
B
  1  
  AA   
  2
  BB  

 DT2
 A 
C
  2  
  10   
  3
  20  


方法１
proc sql;
   create table  DT3 as
   select    DT1.A , B , C
   from      DT1, DT2
   where    DT1.A = DT2.A ;
quit;

データセットDT3
  A  
 B 
  C  
  2
  BB 
 10

 基本構文
 from  データセット1 , データセット2
 where  結合条件
解説
・WHEREによって、変数Aの値が共通するレコードだけを残すようにしてます。
・DT1とDT2で同じ変数名Aを持ってるので、どっちのAを使うのか明確にするため、「DT1.A」とか「DT2.A」と書いてあげる必要がある。




方法２
proc sql;
   create table  DT4 as
   select    DT1.A , B ,  C
   from      DT1  inner join  DT2  on  DT1.A = DT2.A;
quit;


基本構文
  from  データセット1  inner join  データセット2   on  結合条件


解説
結果は方法１と同様。




１１．データセットを横結合する【INNER JOIN】
１３．データセットを横結合する【FULL JOIN】
１４．データセットを縦結合する【UNION】
１５．NULLの扱いに関する注意点


0 件のコメント:
ラベル: , ,

2014年10月30日木曜日

SQLプロシジャ入門10：デカルト積を作る【CROSS JOIN】




デカルト積？という方も例をみるとイメージできると思います。




サンプルデータ
data DT1;
  A=1;  output;
  A=2;  output;
  A=3;  output;
run;

data DT2;
  B="AA"; output;
  B="BB"; output;
run;

データセットDT1
  A   
   1
   2
   3

データセットDT2
 B 
  AA  
  BB


方法１
proc sql;
   create table DT3 as
   select  A, B
   from   DT1 cross join  DT2 ;
quit;

データセットDT3
  A  
B
  1 
  AA   
  2
  AA
  3
  AA
  1 
  BB   
  2
  BB
  3
  BB


 基本構文
 SELECT 変数1 , 変数2 ・・・
 FROM  データセット1  CROSS JOIN データセット2


解説
2つのデータセットにあるレコードの組み合わせを作ってくれる。


方法２
proc sql;
   create table DT4 as
   select  A, B
   from   DT1 , DT2 ;
quit;


基本構文
 FROM  データセット1 , データセット2

解説
方法１と同じ結果になります。
カンマで区切るだけなので、書くのは楽。





0 件のコメント:
ラベル: ,

2014年10月21日火曜日

SQLプロシジャ入門9：値を更新する【UPDATE】


SQLプロシジャによる変数値の更新方法を紹介。


サンプルデータ
data DT1;
   A=1; B="AA"; output;
   A=2; B="BB"; output;
   A=3; B="CC"; output;
run;

 data DT2;
   A=2; B="YY"; output;
   A=3; B="ZZ"; output;
run;

データセットDT1
 A  
B
  1
  AA   
  2
  BB 
  3
  CC 

データセットDT2
 A 
B
  2  
  YY   
  3
  ZZ  



値の更新方法
方法1
proc sql;
   update  DT1
   set       B = "XX"
   where   A = 2 ;
quit;


データセットDT1
  A  
B
  1 
  AA   
  2
  XX 
  3
  CC 


基本構文
 UPDATE  対象のデータセット
 SET        更新する変数 = 格納する値
 WHERE   更新するレコードの条件


方法2
proc sql;
   update  DT1
   set       B = (select B from DT2 where DT1.A=DT2.A)
   where   exists (select * from DT2 where DT1.A=DT2.A);
quit;


データセットDT1
  A  
B
  1
  AA   
  2
  YY 
  3
  ZZ 


解説
他のデータセットを使って更新する方法。
処理内容を翻訳してみると以下のような感じになります。

 SET  B  = (DT1とDT2の変数Aがイコールになる時の、DT2の変数Bの値)
 WHERE     DT1とDT2の変数Aがイコールになるレコードを更新対象とする


間違えに注意
proc sql;
   update  DT1
   set       B = (select B from DT2 where DT1.A=DT2.A);
quit;


データセットDT1
  A  
B
  1
      
  2
  YY  
  3
  ZZ 


解説
よく間違えやすいのが、方法2でWHEREを入れ忘れてしまうケースです。
更新するレコードをWHEREで絞ってないので、DT1とDT2で変数Aがイコールになるものがない場合は欠損値が返されて、変数Bの値を欠損値として更新してしまう。



SQLプロシジャ入門記事一覧
2 件のコメント:
ラベル: , ,

2014年10月5日日曜日

SQLプロシジャ入門8：レコードを削除する【DELETE】


SQLプロシジャによるレコードの削除方法を紹介します。



サンプルデータ
data DT1;
  A=1; output;
  A=2; output;
  A=3; output;
run;
  A  
  1
  2
  3


data DT2;
  A=2; output;
  A=3; output;
run;
  A  
  2
  3


構文1
proc sql;
  delete from DT1
  where A = 2;
quit;

DT1
 A  
  1
  3


基本構文
  DELETE  FROM 対象のデータセット
  WHERE 削除条件


構文2
proc sql;
  delete from DT1
  where A in (select A from DT2);
quit;

DT1
 A  
  1
解説
他のデータセットから選択したレコードを削除条件にすることも出来る。


構文3
proc sql;
  delete from DT1;
quit;

DT1
 A  


解説
削除条件を省略すると、すべてのレコードが削除される。


ただし、SQLによる行削除には注意点あり（行削除の落とし穴を参照）


SQLプロシジャ入門記事一覧
１．変数を選択する【SELECT】
２．レコードを並べ替える【ORDER BY】
７．レコードを追加する【INSERT】
８．レコードを削除する【DELETE】
９．値を更新する【UPDATE】
１０．デカルト積をつくる【CROSS JOIN】

0 件のコメント:
ラベル: , ,

2014年7月23日水曜日

SQLプロシジャ入門7：レコードを追加する【INSERT】


SQLプロシジャによるレコードの追加方法を紹介します。



サンプルデータ
data DT1;
   A=1; B="AA"; output;
   A=2; B="BB"; output;
run;

data DT2;
   A=5; C="AAA"; output;
   A=6; C="BBB"; output;
run;

データセットDT1
 A  
B
  1
  AA    
  2
  BB 

データセットDT2
 A 
 C
  5
  AAA   
  6
  BBB 



構文1
proc sql;
   insert into DT1 (A,B)
   values (3,"CC");
quit;


データセットDT1
 A  
B
  1
  AA    
  2
  BB 
  3
  CC 


基本構文
  INSERT INTO 対象のデータセット  ( 対象の変数 , … )
  VALUES ( 格納したい値 , … )




構文2
proc sql;
   insert into DT1
   values (4,"DD");
quit;


解説
構文1から( 対象の変数 … )部分を省略するとデータセットに格納されてる変数順にVALUESで指定した値を格納していきます。




構文3
proc sql;
   insert into DT1 (A,B)
   select A,C from DT2;
quit;


解説
・SELECT,FROMなどで抽出したレコードを、INSERT INTOで指定したデータセットに追加します。

・INSERTとSELECTで変数名が異なっていても順番で対応づけられます(上記の例ではDT1の変数BにDT2の変数Cの値を格納しています。)

・ちなみに構文2と同様、青字部分を省略して書くことも出来ます。

・今回の例ではログにWARNINGが表示されます。理由は記事の下の方に記載した「注意点」を参照ください。



構文4
proc sql;
   insert into DT1
   set A=7,  B="EE";
quit;


解説
「SET 変数名=格納したい値, …」 という書き方もあります。


[注意点]
追加する値が、もとのLENGTHよりも大きい場合、もとのLENGTHにあわせて文字を切った状態で追加されます。(ログにWARNINGも表示される)



SQLプロシジャ入門記事一覧
１．変数を選択する【SELECT】
２．レコードを並べ替える【ORDER BY】
７．レコードを追加する【INSERT】
８．レコードを削除する【DELETE】
0 件のコメント:
ラベル: , ,

2014年5月16日金曜日

SQLプロシジャ入門6：データセットを作成する【CREATE TABLE】


まだあとちょっと続きます。
しかし今回までの基礎を身につければかなり役に立つと思います。

今回はSQLの結果をデータセットに出力する方法です。


サンプルデータ
data DT1;
input A B;
cards;
1 10
1 15
2 20
2 10
;
 A 
B
  1  
  10   
  1
  15  
  2  
  20   
  2  
  10   



結果をデータセットに出力する。[CREATE TABLE]
proc sql;
   create table  DT2  as
   select *
   from DT1;
quit;
 A 
B
  1  
  10   
  1
  15  
  2  
  20   
  2  
  10   


解説
「CREATE TABLE 出力データセット名 AS」 で結果をデータセットに出力します。




SQLプロシジャ入門記事一覧
１．変数を選択する【SELECT】
２．レコードを並べ替える【ORDER BY】
６．データセットを作成する【CREATE TABLE】
0 件のコメント:
ラベル: , ,

2014年5月7日水曜日

SQLプロシジャ入門5：集計後にレコードを抽出する【HAVING】



5回目はHAVINGです。
WHEREと同じでレコードを抽出する機能なんだけど、抽出するタイミングが異なります。



サンプルデータ
data EXAM;
input NAME$ SUBJECT$ SCORE FLG;
cards;
一郎 国語 20 .
次郎 国語 50 .
三郎 国語 100 1
花子 国語 20 .
一郎 算数 80 .
次郎 算数 30 .
三郎 算数 10 1
花子 算数 99 .
;

NAME
SUBJECT
SCORE
FLG
 一郎  
 国語   
 20  
  .  
 次郎  
 国語   
 50
  .  
 三郎
 国語   
 100  
  1  
 花子  
 国語   
 20  
  .  
 一郎  
 算数   
 80  
  .  
 次郎  
 算数   
 30  
  .  
 三郎
 算数   
 10  
  1  
 花子
 算数   
 99  
  .  

上の例は、各生徒の科目別テストの点数を表してます。
ただし、カンニングした生徒(FLG=1)がいる。



1. 点数が50点未満のレコードを抽出する【HAVING】
proc sql;
   select  *
   from  EXAM
   having  SCORE < 50 ;
quit;


結果ビューア
 NAME 
SUBJECT
SCORE
FLG
 一郎  
 国語   
 20  
  .  
 花子  
 国語   
 20  
  .  
 次郎  
 算数   
 30  
  .  
 三郎
 算数   
 10  
  1  


解説
・HAVINGで抽出条件を指定。
・この例ではWHEREと同じに見えてしまうけど、違いは以下の通り。

  WHERE ・・・ FROMで指定したデータに対する抽出条件
  HAVING ・・・ 集計後のレコードへの抽出条件

具体例は次の例で。



2. 平均が50点未満の科目を調べたい（ただしカンニングした点数は含めない）
【WHERE、GROUP BY、HAVING】
proc sql;
   select     SUBJECT,  avg(SCORE) as AVG_SCORE
   from        EXAM
   where      FLG = .
   group by  SUBJECT
   having     AVG_SCORE < 50 ;
quit;

結果ビューア
SUBJECT
AVG_SCORE
 国語   
 30  


解説
・SELECTにあるAVG関数は平均を求める集計関数。
・具体的な各句の役割は以下の通り。

 ①FROMとWHEREで、データEXAMからカンニングしてないレコードを抽出
 ②SELECTとGROUP BYにより、科目毎の平均点を計算
 ③HAVINGを使って、科目毎の平均点が50点未満のレコードを抽出


このようにグループ化して集計を行ったデータに対して抽出が行えます。



SQLプロシジャ入門記事一覧
１．変数を選択する【SELECT】
２．レコードを並べ替える【ORDER BY】
４．グループ毎に集計する【GROUP BY】
５．集計後にレコードを抽出する【HAVING】
６．データセットを作成する【CREATE TABLE】
７．レコードを追加する【INSERT】
８．レコードを削除する【DELETE】

0 件のコメント:
ラベル: , ,

2014年4月23日水曜日

SQLプロシジャ入門4：グループ毎に集計する【GROUP BY】





4回目はいよいよSQLの得意技であるGROUP BYによるグループ毎の集計について。


サンプルデータ
data DT1;
input A B;
cards;
1 10
1 15
1 20
1 .
2 10
2 15
;

 A 
B
  1  
  10   
  1
  15  
  1
  20 
  1
  .  
  2
  10  
  2
  15 



グループ毎に集計して出力する。[GROUP BY]
proc sql;
   select A ,
            count(B)  as _N  ,
            nmiss(B)  as _MISS  ,
            sum(B)    as _SUM  ,
            min(B)     as _MIN  ,
            max(B)    as _MAX
   from DT1
   group by A;
quit;


結果ビューア
 A 
 _N 
 _MISS 
_SUM 
_MIN 
_MAX 
  1  
  3 
  1 
  45 
  10 
  20 
  2  
  2 
  0 
  25 
  10 
  15 


解説
・まずGROUP BYにグループ化したい変数を指定する。
同じ変数をSELECTにも指定します。

・グループ毎にどの変数にどんな集計をしたいか、SQL用の集計関数をSELECTに記述する。


SQL集計関数は
  COUNT ・・・NULL以外の数
  NMISS  ・・・NULLの数
  SUM     ・・・合計
  MIN      ・・・最小値
  MAX     ・・・最大値
  AVG     ・・・平均値
などがあり、ほかにも簡単な要約統計量を求める関数がいくつかあります。



SQLプロシジャ入門記事一覧
１．変数を選択する【SELECT】
２．レコードを並べ替える【ORDER BY】
４．グループ毎に集計する【GROUP BY】

0 件のコメント:
ラベル: , ,

2014年4月22日火曜日

SQLプロシジャ入門3：レコードを抽出する【WHERE】



3回目はWHEREによるレコードの抽出方法について。



サンプルデータ
data DT1;
  A=2; B="c"; output;
  A=1; B="b"; output;
  A=2; B="a"; output;
run;

 A 
B
  2  
  c   
  1
  b  
  2
  a  


構文
1. レコードを抽出して出力する。[WHERE]
proc sql;
   select *
   from DT1
   where A=2 ;
quit;


結果ビューア
 A 
B
  2  
  c  
  2
  a  


解説
・WHEREでレコードの抽出条件を指定。



2. ここまでのおさらい問題・・・レコードの抽出と並べ替え。[WHERE、ORDER BY]
proc sql;
   select *
   from DT1
   where A=2 
   order by B ;
quit;

結果ビューア
 A 
B
  2  
  a 
  2
  c  


解説
・WHEREでレコードを抽出し、ORDER BYでレコードを並び替える。




SQLプロシジャ入門記事一覧
１．変数を選択する【SELECT】
２．レコードを並べ替える【ORDER BY】

0 件のコメント:
ラベル: , ,
登録: 投稿 (Atom)