SAS忘備録

data DT1;
length NO $3. ALLERGY $20.;
input NO$ ALLERGY$;
cards;
001 花粉症
002 .
003 猫アレルギー
;
run;

データセットDT1
NO   ALLERGY
001    花粉症
002
003    猫アレルギー

やりたい事

サンプルに対して、以下の有無変数を作成したい。

【アレルギー有無】
ALLEYN

【導出方法】
アレルギー（ALLERGY）に記載がある場合「2：有」、それ以外「1：無」とする。

欲しい結果
NO ALLERGY ALLEYN
001 花粉症 2
002 1
003 猫アレルギー 2

１行プログラム

*** 有無変数を1行で作るいくつかの方法 ******************;
data DT2;
    set DT1;

*** 通常のやり方 ;
if ALLERGY^="" then ALLEYN = 2;
else ALEEYN = 1;

    *** 方法① 真偽値 ;
    ALLEYN1 = (ALLERGY^="") +1;

    *** 方法② WHICHN関数 ;
    ALLEYN2 = whichn(1, ALLERGY="", ALLERGY^="");

    *** 方法③ IFN関数 ;
    ALLEYN3 = ifn(ALLERGY^="", 2, 1);

run;

解説
--- 方法① ------------
論理式の結果が「1:TRUE」か「0:FALSE」で返されるので、それに+1してやれば有無変数になります。

--- 方法② ------------
このやり方については「こちら」で解説してます。

--- 方法③ ------------
IFN関数の構文は「IFN(論理式, 論理式がTRUEの戻り値, 論理式がFALSEの戻り値)」でEXCELのIF関数に似てる。
ただし返す戻り値が数値の場合はIFN、文字の場合はIFCと使い分ける必要があります。

１行プログラム番外編

有無変数を「NO、YES」と文字で持たせたい場合は以下のようになります。

data DT2;
set DT1;

length ALLEYN4 ALLEYN5 $10.;

*** 方法① IFC関数 ;
ALLEYN4 = ifc(ALLERGY^="", "YES", "NO");

*** 方法② CHOOSEC関数 ;
ALLEYN5 = choosec((ALLERGY^="")+1, "NO", "YES");

run;

解説
--- 方法① ------------
前述したIFN関数を使うやり方と同様。

--- 方法② ------------
真偽値と「こちら」で紹介したやり方をミックスした方法です。

注意点

今回紹介した方法は、有無の条件が排反である場合に限ります。
（有無が「有」になる条件以外すべて「無」になるような裏と表の関係）

2014年4月11日金曜日

SQLプロシジャで結合条件を省略する方法。

(注意点もあるので最後までご覧ください)

SQLには「自然結合」という結合方法がある。
これはデータ間で共通する変数名を自動で結合条件に入れてくれます。

サンプルデータ作成

data DT1;
input A B;
cards;
1 10
2 20
;

data DT2;
input A C$;
cards;
1 AA
3 BB
;

例

例えば「FULL JOIN」でDT1とDT2を結合したいとする。

*** FULL JOIN ;

proc sql;

create table DT3 as

select coalesce( DT1.A, DT2.A ) as A,

DT1.B,

DT2.C

from DT1 full join DT2 on DT1.A = DT2.A;

quit;

DT3

A B C

1 10 AA

2 20

3 . BB

さて、ここで「FULL JOIN」を自然結合に置き換えてみましょう。

「FULL JOIN」は「NATURAL FULL JOIN」で自然結合になります。

データセット間で「共通する変数名」を自動で結合条件に設定してくれるので、FROMに結合条件は記述不要
ただし、データセット間で「共通する変数名」があってもデータセット間で型が異なっていると、ERRORになり、自然結合が失敗します。
SELECTも「SELECT *」でシンプルな記述にすることが出来ます。このように書くと、各データセットの全変数をSELECTしたうえで、データセット間で変数名が共通する場合はCOALESCE関数で1変数にまとめてくれます（よく分からんって方は、詳細を下の方でも解説してます）

*** NATURAL FULL JOIN ;

proc sql;

create table DT3 as

select *

from DT1 natural full join DT2;

quit;

DT3

A C B

1 AA 10

2 20

3 BB .

「FULL JOIN」と「NATURAL FULL JOIN」の結果を比較すると、変数の順番が違ってますが、それ以外の結果は同じですね。

解説

上の「NATURAL FULL JOIN」の例は、内部で以下のようなプログラムに置き換えられます。

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

まずFROMの「NATURAL FULL JOIN」が「FULL OUTER JOIN (FULL JOIN)」に置き換わっています。
またデータセット間で共通する変数名AがFROMの結合条件に自動で設定されました。
次にSELECTでは、共通変数AをCOALESCE関数で1変数にまとめてくれてます。この関数は「引数のうち最初にNULL以外で登場する値」を返してくれる。

（余談：データステップでは、引数が全て数値の場合COALESCE関数、全て文字の場合COALESCEC関数、と使い分ける必要があるけど、SQLの場合は引数が全て数値または全て文字のどちらでもCOALESCE関数が使える）

ちなみに、

今回は「FULL JOIN」を自然結合に置き換える例でしたが、以下の通り他の結合方法も自然結合に置き換えることが出来ます。

「INNER JOIN」→「NATURAL INNER JOIN」
「LEFT JOIN」→「NATURAL LEFT JOIN」
「RIGHT JOIN」→「NATURAL RIGHT JOIN」
「FULL JOIN」→「NATURAL FULL JOIN」

お役立ち情報

以前紹介した「FEEDBACK」オプションを使って、自然結合がどのような文に展開されているのか、ログに展開することが出来ます。

proc sql feedback;

create table DT3 as

select *

from DT1 natural full join DT2;

quit;

ログ
select COALESCE(DT2.A, DT1.A) as A, DT2.C, DT1.B
from WORK.DT1 full outer join WORK.DT2 on DT2.A = DT1.A;

注意

自然結合は便利だけど、、もし一方のデータセットに変数を追加した場合、
もう一方のデータセットに同じ変数名が存在してると、新たにその変数も自動で結合条件に入ってしまい、当初と異なる動きをしてしまう。

対策として、

例えば、以下のように必要な変数のみKEEP等で保持しておけば、上記のような不測の事態にも備えることが出来ます。

proc sql;

create table DT3 as
select *
from DT1 (keep=A B) natural full join DT2 (keep=A C);

quit;

こういった不確定要素もあるので、自然結合を用いる際は、最大限の注意を払う必要があります。

注意その2

SQLプロシジャの「NATURAL JOIN」で3つ以上のデータセットを結合すると意図した結果にならない。

2014年4月10日木曜日

SQLプロシジャで「SELECT *」等の省略文をログに展開する。

サンプルデータ作成

data DT1;
input A B;
cards;
1 10
2 20
;
run;

data DT2;
input A C$;
cards;
1 AA
2 BB
;
run;

省略文をログに出力

proc sql feedback ;
create table DT3 as
select DT1.*,
DT2.C
from DT1 join DT2 on DT1.A=DT2.A;
quit;

OUTPUT
A B C
1 10 AA
2 20 BB

ログ
select DT1.A, DT1.B, DT2.C
from WORK.DT1 inner join WORK.DT2 on DT1.A = DT2.A;

FEEDBACKオプションを入れることで、ログに省略した部分の詳細が表示されます。

役に立つケースとして個人的には、
他の人が書いたSQL文を読むときに、このオプションをつけると解読する取っ掛かりになる場合がある。
あとはこんな文に置き換えられてるのか！という発見があったりして面白いです。

2014年4月4日金曜日

SQLグラフ

いろんなとこで何回か見かけたことあるSQLで超簡易グラフを作る技、、というか遊び。
役に立つというものではないけど、上手いなぁーと思ったので紹介したいです。

まず、適当に1～5までの値をとるサンプルデータを作成

data DT1;

call streaminit(123);

do i = 1 to 100;

V1 = ceil(rand('uniform')*5);

output;

end;

run;

SQLで超簡易的な横棒グラフを作成

proc sql;
select V1, repeat("*",count(*)-1)
from DT1
group by V1;
quit;

ムダのないかっこいいプログラムだと思います。

2014年4月3日木曜日

時間値を文字変換する時、先頭にゼロを埋める方法「1:30→01:30」

時間値のフォーマットとして「TIME」が有名だけど、
文字変換する際、「1:30」ではなく「01:30」のようにゼロを埋めて出力したい場合がある。
この場合、同じ時間値のフォーマット「TOD」が使えます。
意外に知られてない気がします。

data DT1;
V = "01:30"t;

V1 = put(V,time5.);
V2 = put(V,tod5.);
run;

データセットDT1

V V1 V2

5400 1:30 01:30

2014年4月2日水曜日

ログ・アウトプットを外部ファイルに出力する。

「ログウィンドウ」と「アウトプットウィンドウ」の出力内容を、外部ファイルに保存する方法。

方法① PRINTTOプロシジャ

proc printto log = "保存するログのパスとファイル名.log"
print = "保存するアウトプットのパスとファイル名.lst" new;
run;

・上記以降に実行したプログラムのログ・アウトプットが指定したパスに保存されます (「ログウィンドウ」と「アウトプットウィンドウ」には出力されなくなる)

・newオプションは、すでに同名ファイルが存在する場合、上書きしてくれる。
(指定しないと、追加書き込みになる)

最後に、以下を記述してログとアウトプットの出力先をデフォルトに戻しましょう。

proc printto; run;

方法② DMステートメント

（注意喚起追記）
最近、DMステートメントでログ・アウトプットがうまく保存されない現象が起きました。
詳細不明のため、ご注意ください！

以下ステートメントをプログラムの最後に実行すると、それまでに出力された「ログウィンドウ」と「アウトプットウィンドウ」の中身を指定したパスに保存してくれる。

dm log 'file "保存するログのパスとファイル名.log" replace';

dm output 'file "保存するアウトプットのパスとファイル名.lst" replace';

replaceは、すでに同名ファイルが存在する場合、上書きしてくれる。

ただしDMステートメントは、、

・バッチ実行下では動作しない。

・すべての環境下で動作するか不明のため、使用する場合は各環境下で動作確認を行ってください。
・「dm output 'file …」という書き方はテキスト形式のアウトプットを保存する方法です。SAS9.3以降アウトプットの形式がデフォルトでHTMLになったため、このまま書いてもうまく動作しません。
プログラムの先頭に「ods listing;」と書いてテキスト形式のアウトプットも出力されるように設定しておく必要があります。

ちなみに以下を記述しておくとSAS上の「ログウィンドウ」と「アウトプットウィンドウ」の中身を初期化（クリア）してくれるので便利。

dm log 'clear' ;

dm output 'clear' ;

登録: 投稿 (Atom)