Proc sqlを極める(11)　ーconstraintー

データセットを作成する際、各変数に予め決められた情報のみが格納されるようにプログラムすることはデータの品質管理上重要です。
例えばIDのような主キーとなる変数には重複がないユニークな値が格納されるべきですし、日付の文字列を格納する変数には日付として解釈できる文字列が格納されるべきです。

データがすべて揃っていて、欠損や不整合がないことを情報処理分野ではData Integrityと呼んでいます。FDAやEMAはこのData Integrityに関するガイドラインを発表しており、
Data Integrityの保証は統計解析業務でも重要な要素となっています。

SQLには変数に格納される条件を定義するconstraintというしくみがあります。これを活用すると定義外のデータが混入することを防ぐことができ、Data integrityの保証に寄与できるかもしれません。

一見すると有用な機能のように見えますが、結論をいうと入力データが更新する度にデータセットを再作成するような手順の場合はあまりメリットがなく、むしろデータステップを活用したほうが利便性が高いです。

ただしSQLのconstraintの機能は一般的なRDMSでもありますので覚えておいて損はないかと思います。

Constraintとは

constraintとは日本語だと「制約」と訳されます。つまりデータセットに格納できるデータに制約を設け、意図しない形式のデータが入ってくるのを防止することができます。

以下のコードはconstraintを定義したデータセットTestを新規作成します。USUBJIDとSEQは主キーとして設定され、欠損値とキーの重複を許容しません。

proc sql noprint;
create table test (
USUBJID char(10),
SEQ num,
constraint prim_key
primary key(USUBJID, SEQ) message="キーが重複してます! ");
quit;

constraintの構文は以下の通りです。

constraint < 制約名 >
<制約の種類 > (制約の定義）
message = <制約に合致しなかったときに表示するメッセージ>;

作成したデータセットにTest2のデータを追加してみます。proc appendは指定したデータセットに別のデータセットのレコードを追加するプロシジャです。SQLのinsertでも同様の操作ができます。

data test2;
length USUBJID $10;
do USUBJID = "A001", "B002";
do SEQ =1 to 10;
output;
end;
end;
run;
proc append base=test data=test2;
run;
*SQLでやるならこうなる;
proc sql noprint;
insert into test
select USUBJID, SEQ from test2;
quit;

この処理は制約条件を満たしているので正常に処理が完了します。

ただしもう一度test2のデータをtestに追加するとUSUBJIDとSEQが重複するので主キーの制約に適合しないため、レコードの追加が失敗します。
制約に適合しなかった場合はあらかじめ指定したメッセージがログに表示されます。

注意点はproc appendとsqlは制約を満たせなかったレコードが見つかった場合の挙動が違います。

proc appendは一部のデータが制約を満たせなかった場合でも、制約を満たしているデータの挿入は成功します。

一方proc sqlは一つでも制約を満たせなかったデータがあればそれ以前に挿入された正常データは削除され、挿入操作以前の状態に戻ります。proc sqlのほうが保守的な挙動を示すので、この手の操作をしたいのであればsqlで実施したほうが良いかもしれません。

想定される利用方法としては、制約を付与した空のデータセットを新規作成した後で前処理済みのデータを挿入し、永久SASデータセットとして保存する形になると思います。

constraintの種類

constraintには以下の種類があります。

Check制約の定義はSAS関数を使用することができます。そのためprx関数を使って正規表現パターンマッチも制約に加えることができます。
以下のコードはUSUBJIDに「大文字アルファベット+３桁の数字」の制約を、SEQには「自然数（すなわち0より大きい整数)」の制約を付与しています。

proc sql noprint;
create table check_test (
USUBJID char(10),
SEQ num,
constraint check1 check(prxmatch('/[A-Z]\d{3}/', strip(USUBJID)) > 0) message="USUBJIDは大文字アルファベット+3桁の数字でなければなりません",
constraint check2 check(SEQ>0 and SEQ=int(SEQ)) message="SEQは自然数でなければなりません");
*挿入できる;
insert into check_test
values ("C001", 1)
values("D001", 1);
quit;
*挿入できない;
proc sql noprint;
insert into check_test
values("D003", 1.5)
;
*挿入できない;
proc sql noprint;
insert into check_test
values("D003", -1)
;
quit;
*挿入できない;
proc sql noprint;
insert into check_test
values("ABCD", 1)
;
quit;

実行すると、以下のようなエラーメッセージが表示されます。

 ERROR: SEQは自然数でなければなりません
データセットWORK.CHECK_TESTへの追加/更新に失敗しました。データ値が一貫性制約check2に適合しま
せん。
ERROR: USUBJIDは大文字アルファベット+3桁の数字でなければなりません
データセットWORK.CHECK_TESTへの追加/更新に失敗しました。データ値が一貫性制約check1に適合しま
せん。

Restrictは参照元のデータセットの指定した変数に存在しない値を持つレコードが挿入されるのを防ぐことができます。

例えば患者背景情報データセットの患者IDに存在しない患者IDのレコードが有害事象データセットに挿入された場合、その挿入を無効にすることができます。

今回は簡略にするためにUSUBJID(患者ID)のみを保持した状態でやってみます。

患者背景データセットを作成し、USUBJIDをprimary keyとして設定します。
既存のデータセットに制約を追加するときはadd constraintを使用します。

*患者背景;
data back;
length USUBJID $10;
do USUBJID = "A001", "B002";
output;
end;
run;
proc sql noprint;
*制約を追加;
alter table back
add constraint prim_key primary key(USUBJID);
quit;

次に有害事象データセットaeにforeign keyを設定します。参照先は先ほど作成したbackとします。

*参照キーを設定する;
proc sql noprint;
create table ae (
USUBJID char(10),
constraint foreign1 foreign key (USUBJID) references back)
;
quit;

aeにUSUBJID=C003のレコードを追加しようとすると、参照先のbackにはUSUBJID=C003のレコードが存在しないため、
エラーとなります。

proc sql noprint;
insert into ae
values("C003");
quit;
 ERROR: オブザベーションは追加/更新されていません。外部キーforeign1に対して、一致するプライマリ
キー値が見つかりません。

注意点としてはrectrictの参照先のデータセット（今回だとback)は変更および削除ができません。
変更あるいは削除したい場合は事前に制約を削除する必要があります。

既存のデータセットの制約を削除するにはdrop constraintを使用します。

*参照キーを設定している場合、制約を削除しないとデータセットの変更削除はできない;
proc sql noprint;
alter table ae
drop constraint foreign1;
alter table back
drop constraint prim_key ;
quit;

習得優先度は低い

一見便利そうにも見えるconstraintなのですが、データステップでも同様のチェックは実施できるので、別に無理してsqlを使わなくてもいいと思います。この機能
あくまでデータベース向けの機能なんですよね・・・

putステートメントで任意の警告を表示できますし、重複チェックもproc sortを活用すれば一応実現できます。そして何より使い慣れたデータステップのほうが
自由度も高いです。

なお今回の制約条件の定義はproc datasetsでも実施することができます。ただsqlとできることは同じなので、sql同様使う必要はあまりないんじゃないかな。