SAS忘備録: SQL: UNION

ラベル SQL: UNION の投稿を表示しています。すべての投稿を表示

2015年1月21日水曜日

データセットを縦結合する際、変数名が同じでlengthが異なってたら、最大のlengthに合わせる便利技

長いタイトルですが、SQLのいいとこ取りした便利技の紹介です。

Sample Data

data DT1;
length A $2. B $1.;
input A$ B$ ;
cards;
01 a
02 b
02 b
;
run;

data DT2;
length A $1. B $2.;
input A$ B$;
cards;
3 cc
4 dd
;
run;

DT1

A	B
01	a
02	b
02	b

DT2

A	B
3	cc
4	dd

サンプルデータをまずSETで結合してみます。

data OUT1;
set DT1 DT2;
run;

ログ
WARNING: 入力データセットによって、変数Bに複数の長さが指定されました。データの切り捨てが発生します。

A	B
01	a
02	b
02	b
3	c
4	d

結果を見ると、変数Bの値が、一部文字切れしちゃってますね。

これは、
「set DT1 DT2」で最初に指定したデータセットDT1にある変数のlengthが、結合後のデータセットに割り当てられるからです。

つまり、変数Bのlengthが、DT1は「$1」、DT2は「$2」と異なっていますが、
上記青字の性質によって、結合時に「$1」が割り当てられます。

なので、DT2から持ってきた変数Bは、length「$1」に合わせて文字を切ってしまいます。

解決法

正攻法として、SETする時にlengthを設定しなおせばokですが、、、

data OUT2;
length A B $3.;
set DT1 DT2;
run;

「SQLプロシジャ入門14：データセットを縦結合する【UNION】」で紹介した「OUTER UNION CORR」を使えば、
なんと結合するデータセットの中で最大のlengthを勝手に設定してくれます。

proc sql;
create table OUT3 as
select * from DT1
outer union corr
select * from DT2;
quit;

A	B
01	a
02	b
02	b
3	cc
4	dd

ただし、SETステートメントと異なる動きあり。

まず、以下のプログラムと結果を見てみましょう。

*** Sample Data ;
data DT1;
A = .;
run;

data DT2;
format A yymmdd10. ;
A = '01jan2015'd ;
run;

DT1

A
.

DT2

A
2015-01-01

*** SETステートメントによる結合 ;
data OUT1;
set DT1 DT2;
run;

A
.
2015-01-01

*** 「OUTER UNION CORR」による結合 ;
proc sql;
create table OUT2 as
select * from DT1
outer union corr
select * from DT2;
quit;

A
.
20089

2つの結合結果を比較してみると、2行目の値が異なってますね。

これは「SETステートメント」の方は、format 「yymmdd10」が割り当てられていて、

「OUTER UNION CORR」の方は、formatが割り当てられてないというだけで、
値自体は同じものです。

リファレンスから探せなくて確かではないですが、結合したデータセットの format, informat は、

「SETステートメント」：最初のデータセットDT1にformat, informatが割り当てられてなければ、次のデータセットDT2のformat, informatを割り当てている模様。
「OUTER UNION CORR」：最初のデータセットDT1のものを割り当てている模様。

他にも差異があるかもしれません。

利用する際はいろいろテストしたり、期待通りの結果になるかご確認ください。

この違いを気にしないシチュエーションであれば、超便利なテクニックですね。

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