SASデータエンジニアであるために必要な最低限のSQL知識まとめ

SASとは

SAS(Statistical Analysis System)とは、データベース等のあらゆるデータの
読み込み、加工、統計解析、出力等を可能とするソフトウェアです。

SASでSQLを使う

SASでは特有のプログラミング言語を用いますが、SQLにも対応しています。
本記事では、SASでSQLを扱う上で最低限必要となる基礎知識をまとめます。

---

～基本構文～

proc sql;
    /*ここに処理を書き込む*/
quit;

SASではSQLプロシジャを使うことで、SQLによるデータの操作が可能です。
「proc sql;」 と 「quit;」で実行したい処理を囲みます。
ちなみに、この「proc sql～quit」の間には複数の処理を書き込めます。
後述する「select」や「insert」をまとめて書き込んでも問題ありません。

---

～SELECT～

「SELECT」はデータの参照です。

data work.sample1;
	A = 'aaa'; B = 1; C = 100; output;
	A = 'bbb'; B = 2; C = 200; output;
	A = 'bbb'; B = 1; C = 300; output;
	A = 'bbb'; B = 3; C = 400; output;
run;

work.sample1

A	B	C
aaa	1	100
bbb	2	200
bbb	1	300
bbb	3	400

例えば上記のようなデータを参照したい場合、

proc sql;
	select 
/* 	ここに参照する変数を書く */
		A
		,B
	from 
/* 	ここに参照するデータセットを書く */
		work.sample1
	where
/* 	ここに抽出条件を書く */
		A = 'bbb'
	order by
/* 	ここに並び替える変数を書く */
		B desc
	;
quit;

このように記述します。
いくつかのステートメントに別れているので順に説明します。

• select
  参照したい変数をカンマ（,）区切りで指定します。
  ここで変数を指定せずにアスタリスク（*）を指定すると
  全ての変数を参照できます。
  このステートメントの記述は必須です。
• from
  参照するデータセットを記述します。
  このステートメントの記述は必須です。
• where
  オブザベーションの抽出条件を記載します。
  上記の例だと変数Aの値が'bbb'のオブザベーションのみ抜き出しています。
  複数条件による指定も可能です。
  その場合は「and」「or」等の論理演算子を用いて条件を追加していきます。
  このステートメントの記述は任意です。
• order by
  並び替えたい変数を指定します。
  上記だと変数Bをベースにオブザベーションを並び替えます。
  降順にしたい場合は変数名の後に「desc」と記述します。
  何も記載しなければ昇順となります。
  変数を複数指定することも可能です。
  その場合は並び替える優先順位が高いものから順に
  カンマ（,）区切りで変数を指定します。
  このステートメントの記述は任意です。

A	B
bbb	3
bbb	2
bbb	1

結果は上記のようになります。
また、新たな変数を作成することも可能です。

proc sql;
	select 
        /*↓変数の計算*/
		B + C as D
        /*↓固定値*/
		,'infront' as E
	from
		work.sample1
	;
quit;

上記では変数Bと変数Cを足した値を新たに変数Dとして作成しています。
また、変数Eのように固定の値（文字値でも数値でも可）を
入れ込んで新たな変数とすることもできます。
作成した変数の後に「as 変数名」で変数名を指定します。
既存の変数名の変更も可能です。
結果は下記になります。

D	E
101	infront
202	infront
301	infront
403	infront

~group by--having~

次は「group by」と「having」です。
まずはgroup byから。
group byは「集約関数」とともに使用しグループ毎の集計を行います。
説明するよりも見たほうが早いと思うのでサンプルコードをご覧ください。

data work.sample2;
	input A $ B C;
	cards;
	a 1 20
	a 1 30
	b 1 45
	b 1 60
	b 2 10
	c 1 40
	;
run;

work.sample2

A	B	C
a	1	20
a	1	30
b	1	45
b	1	60
b	2	10
c	1	40

proc sql;
	select
		A
		,B
		,SUM(C) as SUM_C
	from 
		work.sample2
	group by
		A,B
	;
quit;

A	B	SUM_C
a	1	50
b	1	105
b	2	10
c	1	40

変数CをSUM()で括っています。このSUM()が集約関数です。
関数には様々なものがありますが、
SUMという関数は値を合計値を計算する機能を持っています。
ここでは変数Cを()で括ることで変数Cを合計値をだしています。
そこにgroup byを指定することで指定した変数毎の合計値を出してくれます。
つまりここでは変数Aと変数Bが同じオブザベーションをひとまとめにして
変数Cの合計値を表示しています。

では次はhavingです。
havingはwhereと同じで抽出条件を指定するステートメントですが
抽出のタイミングが異なります。

proc sql;
	select
		A
		,B
		,SUM(C) as SUM_C
	from 
		work.sample2
	where
		C < 60
	group by
		A,B
	having 
		SUM_C >= 40
	;
quit;

A	B	SUM_C
a	1	50
b	1	45
c	1	40

group byで紹介したコードに少し付け加えました。
結果を見比べてみてください。
havingでSUM_Cが40以上のレコードに絞り込んでいます。
すなわちhavingは集計した変数に対して絞り込みをかけられるということなのですが、
前回の結果と見比べてみると2オブザベーション目のSUM_Cの値が違います。
これはwhereで変数Cが60以上のレコードに絞り込んでいるからです。
上記のコードの処理の流れとしては、

1. whereでwork.sample2から変数Cが60未満のレコードを抽出。
2. SUM()で変数Cの合計値を算出。
3. havingで変数SUM_Cが40以上のレコードを抽出。

という順番になります。
抽出のタイミングが異なるというのはこういう事で、
whereはgroup byによる集計前のデータに対して抽出をかけるのに対して
havingは集計後のデータに対する抽出が可能です。
少しややこしいと感じる方は、
havingは集計を行った変数に対して抽出をかけるときに使う。
と思っていただければよいかと思います。

~データセットの結合~

続きましては、データセットの結合です。
結合というのは2つのデータセットを一つにまとめる処理のことです。
結合にはいくつか種類があるのですが、
ここでは特に使用頻度が多いと思われる結合方法を3つご紹介します。

~FULL JOIN~

「FULL JOIN」は、結合の際に指定したキー変数が紐づいても紐づかなくても
両側の全てのオブザベーションを残す結合方法です。

data work.sample3_1;
	input A $;
	cards;
	a
	b
	c
	;
run;

data work.sample3_2;
	input B $;
	cards;
	b
	c
	d
	;
run;

work.sample3_1

A
a
b
c

work.sample3_2

B
b
c
d

proc sql;
	select 
		t1.*
		,t2.*
	from 
		work.sample3_1 t1 
	full join 
		work.sample3_2 t2
	on 
		t1.A = t2.B
	;
quit;

A	B
a
b	b
c	c
	d

fromに参照するデータセットを記述し、
「full join」に続いて結合したいデータセットを記述、
「on」で結合の条件を記載します。
結合にはいくつか種類があるといいましたが基本の構文は全て同じで
ここでいう「full join」の部分が結合の方法によって変わります。
また、fromとfull joinの後に記載したデータセットの後ろに
「t1」「t2」と書かれています。これはそのデータセットの参照名です。
ここでは「work.sample3_1」を「t1」と呼ぶということです。
つけなくても問題はありませんがその場合は「t1」の代わりに、
「sample3_1」といちいち書かなくてはなりません。
面倒なので参照名は指定したほうがよいでしょう。
この処理を要約すると
t1の変数Aとt2の変数Bが同じ値のオブザベーションを結合するという意味です。

~LEFT JOIN~

続いて「left join」です。
left joinは結合する片側のデータセットのオブザベーションを残す方法です。

proc sql;
	select 
		t1.*
		,t2.*
	from 
		work.sample3_1 t1 
	left join 
		work.sample3_2 t2
	on 
		t1.A = t2.B
	;
quit;

A	B
a
b	b
c	c

結果をfull joinの時と見比べてみましょう。
4オブザベーション目が消えています。
t1のオブザベーションだけt2と紐づこうが紐づくまいが残して、
t2はt1と紐づくオブザベーションがなければ消してしまいます。
コードでいうと、left joinの左側に記述したデータセットは残して
右側に書いたデータセットは残しません。
わかりやすく書くとこうですね。

proc sql;
	select 
		t1.*
		,t2.*
        /*↓こっちは残す*/　　　　　　/*↓こっちは残さない*/
    from work.sample3_1 t1 left join work.sample3_2 t2
		t1.A = t2.B
	;
quit;

~INNER JOIN~

最後は「inner join」です。
これは結合するどちらのデータセットも紐づいたオブザベーションしか残しません。
もうどうなるかは分かるかもしれませんが、一応サンプルです。

proc sql;
	select 
		t1.*
		,t2.*
	from 
		work.sample3_1 t1 
	inner join
		work.sample3_2 t2
	on 
		t1.A = t2.B
	;
quit;

A	B
b	b
c	c

このようになります。
両側に同じ値のある'b'と'c'のオブザベーションしか残りません。

~create table~

ここまでいくつかサンプルでSQLを紹介してきましたが、
これらは全て結果をビューに表示するだけです。
SQLで作成したデータをデータセットとして作成するには
「create table」を使います。
使い方は簡単でSQL冒頭で「create table 作成するデータセット名 as」と記述するだけです。

proc sql;
	create table work.DS1 as
	select 
		*
	from 
		work.sample1
	;
quit;

work.DS1

A	B	C
aaa	1	100
bbb	2	200
bbb	1	300
bbb	3	400

上記のようなデータセットがworkに作成されます。
ちなみにデータセット名の後の「as」ですが、
省略するとエラーになってしまうので注意してください。

---

～INSERT～

「insert」はデータセットにデータを追加することが出来ます。

data work.sample;
	A = 1; B = 'A'; output;
	A = 2; B = 'B'; output;
run;

work.sample

A	B
1	A
2	B

ここにオブザベーションを追加してみましょう。

proc sql;
    insert into work.sample(A,B)
    values (3,'C');
quit;

A	B
1	A
2	B
3	C

insert intoの後にデータを追加するデータセット、()の中には
データを追加する変数を指定します。
そしてvaluse()に追加するデータを指定します。
順番はinsert intoで指定した変数と同じ順番で指定してください。

---

～DELETE～

「delete」はデータの削除です。
では「insert into」で作成したサンプルデータを削除してみます。

work.sample

A	B
1	A
2	B
3	C

proc sql;
    delete from work.sample
    where A = 2;
quit;

A	B
1	A
3	C

変数Aが'2'のオブザベーションが削除されました。
「delete from」の後に削除対象のデータセット名、
「where」の後に削除したいオブザベーションの条件です。
ちなみにwhereを省略すると全てのオブザベーションが削除されます。
不用意に使わないよう注意しましょう。

---

～UPDATE～

「UPDATE」はデータの更新です。
では「insert into」で作成したサンプルデータを更新してみます。

work.sample

A	B
1	A
2	B
3	C

proc sql;
    update work.sample
    set B = 'Z'
    where A = 2;
quit;

A	B
1	A
2	Z
3	C

変数Bの2オブザベーション目が更新されました。
構文としては
「update」の後に更新するデータセット名、
「set」の後に更新したい変数 = 更新後の値、
「where」の後に更新するオブザベーションの条件です。

---

まとめ

SASでSQLを使う上での基礎知識をまとめてきましたが
1つのプロシジャで広範囲なデータ操作が可能であることが
理解していただけたかと思います。
正直ここまで紹介した大抵の機能はデータステップや
他のプロシジャでも代用可能ですが、
SQLはSAS以外でも使える汎用的な言語です。
使ったことのない方は一度使ってみてはいかがでしょうか。