2014年4月28日月曜日

MEANSプロシジャで結果ビューアに出る結果と同じ形のデータセットを作る。



SAS9.3からMEANSプロシジャに「STACKODSOUTPUT (STACKODS でも可)」というオプションが追加されました。
これは結果ビューアの出力と似た形のデータセットを作ることが出来るオプションです。




サンプルデータ作成

data DT1;
input SEX  HEI  WEI;
cards;
1 165 70
1 155 50
2 160 40
2 150 50
2 149 60
;




3つのデータセット出力方法

STACKODSとそれ以外の出力方法もあわせて紹介していきます。


方法1 ・・・ OUTPUTステートメント
proc means data=DT1 n mean  nway;
   var  HEI  WEI;
   class  SEX / missing;
   output  out=SUM  n= mean= / autoname;
run;

データセットSUM
SEX _TYPE_ _FREQ_ HEI_N WEI_N HEI_Mean WEI_Mean
1 1 2 2 2 160 60
2 1 3 3 3 153 50



方法2 ・・・ ODS OUTPUT
ods output summary=SUM2;
proc means data=DT1 n mean  nway;
   var  HEI  WEI;
   class  SEX / missing ;
run;
ods output close;

データセットSUM2
SEX  NObs VName_HEI  HEI_N  HEI_Mean  VName_WEI  WEI_N  WEI_Mean 
1 2 HEI 2 160 WEI 2 60
2 3 HEI 3 153 WEI 3 50



方法3 ・・・ ODS OUTPUTとSTACKODSの組み合わせ (SAS9.3から)
ods output summary=SUM3;
proc means data=DT1 n mean  nway  STACKODS ;
   var  HEI  WEI;
   class  SEX / missing ;
run;
ods output close;

結果ビューアの出力











データセットSUM3
SEX NObs _control_ Variable N Mean
1 2 HEI 2 160.000000
1 2 WEI 2 60.000000
2 3 1 HEI 3 153.000000
2 3 WEI 3 50.000000




要望としては、将来他のプロシジャでも対応してほしいですね。
FREQプロシジャとか。。

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プロシジャ入門記事一覧
1.変数を選択する【SELECT】
2.レコードを並べ替える【ORDER BY】
4.グループ毎に集計する【GROUP BY】

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プロシジャ入門記事一覧

1.変数を選択する【SELECT】
2.レコードを並べ替える【ORDER BY】

2014年4月18日金曜日

SQLプロシジャ入門2:レコードを並べ替える【ORDER BY】


2回目はレコードの並び替えについて。


サンプルデータ

data DT1;
  A=2; B="a"; output;
  A=1; B="b"; output;
  A=2; B="c"; output;
run;


 A 
B
  2  
  a   
  1
  b  
  2
  c  



構文

1. レコードを並び替えて出力する。[ORDER BY]
proc sql;
   select *
   from DT1
   order by A, B ;
quit;


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


解説
・ORDER BYで指定した変数の順で行を並び替える



2. レコードを降順に並び替えて出力する。[ORDER BY ● DESC]
proc sql;
   select *
   from DT1
   order by B  desc;
quit;


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


解説
・ORDER BYでDESCを指定した変数は降順で並び替えられる



SQLプロシジャ入門記事一覧

1.変数を選択する【SELECT】
2.レコードを並べ替える【ORDER BY】
3.レコードを抽出する【WHERE】
4.グループ毎に集計する【GROUP BY】
5.集計後にレコードを抽出する【HAVING】
6.データセットを作成する【CREATE TABLE】
7.レコードを追加する【INSERT】
8.レコードを削除する【DELETE】

SQLプロシジャ入門1:変数を選択する【SELECT】



SASユーザーがSQLプロシジャを使うメリットは、データステップ等では面倒なグループ化処理や複雑なデータ結合が出来るところだと思います。

ということで、数回に渡って基本を紹介したいと思います。まずは「SELECT」から。



サンプルデータ

data DT1;
   A=1;  B=10;  output;
   A=2;  B=20;  output;
run;

 A 
B
  1  
  10   
  2
  20  



構文

1. SQLプロシジャの基本形。
proc sql ;
   SQL文 ;
quit ;



2. 変数を選択して結果ビューアに出力する。[SELECT, FROM]
proc sql ;
   select  A, B
   from    DT1  ;
quit ;

結果ビューア
 A 
B
  1  
  10   
  2
  20  


解説
・SQL文の中のセミコロン(;)は最後に1つ入れるだけでOK。
・SELECTには出力する変数を列挙。
・FROMには対象のデータセット名を指定。




3. 全変数を選択して結果ビューアに出力する。[SELECT *]
proc sql ;
  select  *
  from  DT1;
quit ;

結果ビューア
 A 
B
  1  
  10   
  2
  20  


解説
・アスタリスク(*)で全変数という意味になる。




4. 新しい変数を作成する。[SELECT ● as △]
proc sql;
  select   A     as  V1 ,
              A+B as V2 ,
              123  as V3
  from  DT1;
quit;

結果ビューア
  V1  
 V2 
  V3  
  1
  11  
 123
  2
  22 
 123


解説
・「変数や式または定数  as  新規変数名」で新しい変数を作れる。



おまけ
PROC SQLでの変数ラベルの挙動


SQLプロシジャ入門記事一覧

1.変数を選択【SELECT】
2.レコードを並べ替える【ORDER BY】
3.レコードを抽出する【WHERE】

2014年4月16日水曜日

1行プログラムその5:有無変数

有無変数を1行でつくる方法を紹介。

サンプルデータ作成

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行プログラム

*** 有無変数を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と使い分ける必要があります。


1行プログラム番外編

有無変数を「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       'print  file="保存するログのパスとファイル名.log"  replace';
        dm output  'print  file="保存するアウトプットのパスとファイル名.lst"  replace';


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



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

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





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


         dm  log  'clear' ;
         dm  output  'clear' ;