SAS SQL

SAS SQL EPS SA1 山野辺

SQL • マクロ変数の作成 SQLの結果をマクロ変数に格納する - into: • 9.3以降，色々なオプションが追加されている • 集合演算子

SQLとは • DBMS上でデータベースをコントロールするた めの言語で，RDBに命令を投げて，結果が反映 される →テーブルを操作するための言語で， SASのように行単位で処理する言語ではない

Medianが取れる • SQLではTableを参照し，集計はするものの medianは取れなかった． • 9.4からはmedianに対応し，SAS関数と同じのり で書ける proc sql; select median(age) from sashelp.class ; quit;

どんなときに使う？ • 要約統計量は，多くの場合以下を求められる ・mean ・sd ・median ・min ・max

どんなときに使う？ Proc sql; create table DATA1 as select distinct sex, count(name) as n , mean(age) as mean , std(age) as sd ,median(age) as median , min(age) as min ,max(age) as max from sashelp.class group by sex ; Quit;

どんなときに使う？ Proc sql; create table DATA1 as select distinct sex, count(name) as n , mean(age) as mean , std(age) as sd ,median(age) as median , min(age) as min ,max(age) as max from sashelp.class group by sex ; Quit;

どんなときに使う？ Proc sql; create table DATA1 as select distinct sex, count(name) as n , mean(age) as mean , std(age) as sd ,median(age) as median , min(age) as min ,max(age) as max from sashelp.class group by sex ; Quit; SASにはproc summary という素晴らしい プロシジャがあるので， proc summaryを使って ください．

空白を詰める proc sql noprint; select distinct max(age) into: m_age from sashelp.class where sex='男子' ; quit; %put &=m_age; 結果： M_AGE= 16

空白を詰める proc sql noprint; select distinct max(age) into: m_age from sashelp.class where sex='男子' ; quit; %put &=m_age; 結果： M_AGE= 16 [□ □ □16]

空白を詰める proc sql noprint; select distinct max(age) into: m_age trimmed from sashelp.class where sex='男子' ; quit; %put &=m_age; 結果： M_AGE=16

空白を詰める proc sql noprint; select distinct max(age) into: m_age trimmed from sashelp.class where sex='男子' ; quit; %put &=m_age; 結果： M_AGE=16 [16]

複数項目の変数を出す proc sql noprint; select distinct sex ,max(age) into:m_sex ,:m_age from sashelp.class where sex=“男子” group by 1 ; quit; %put &=m_sex &=m_age ; 結果： M_SEX=男子 M_AGE=16

複数行の結果を出す proc sql noprint; select distinct sex ,max(age) into:m_sex1-:m_sex2 ,:m_age1-:m_age2 from sashelp.class group by 1 ; quit; %put &=m_sex1 &=m_age1 ; %put &=m_sex2 &=m_age2 ; 結果： M_SEX1=女子 M_AGE1=15 M_SEX2=男子 M_AGE2=16

複数行の結果を出す proc sql noprint; select distinct make,max(length) into:m_mak1-:m_mak38 ,:m_len1-:m_len38 from sashelp.cars group by 1 ; quit; %put &=m_mak1 &=m_len1 ; %put &=m_mak2 &=m_len2 ; %put &=m_mak3 &=m_len3 ; %put &=m_mak4 &=m_len4 ; … M_MAK1=Acura M_LEN1=197 M_MAK2=Audi M_LEN2=204 M_MAK3=BMW M_LEN3=204 M_MAK4=Buick M_LEN4=207 M_MAK5=Cadillac M_LEN5=221 …

複数行の結果を出す proc sql noprint; select distinct make,max(length) into:m_mak1,:m_len1from sashelp.cars group by 1 ; quit; %put &=m_mak1 &=m_len1 ; %put &=m_mak2 &=m_len2 ; %put &=m_mak3 &=m_len3 ; %put &=m_mak4 &=m_len4 ; … M_MAK1=Acura M_LEN1=197 M_MAK2=Audi M_LEN2=204 M_MAK3=BMW M_LEN3=204 M_MAK4=Buick M_LEN4=207 M_MAK5=Cadillac M_LEN5=221 …

複数行の結果を出す proc sql noprint; select distinct make,max(length) into:m_mak1,:m_len1from sashelp.cars group by 1 ; quit; 複数のマクロ変数に持たせると 使い勝手が悪い・・・ %put &=m_mak1 &=m_len1 ; %put &=m_mak2 &=m_len2 ; %put &=m_mak3 &=m_len3 ; %put &=m_mak4 &=m_len4 ; … M_MAK1=Acura M_LEN1=197 M_MAK2=Audi M_LEN2=204 M_MAK3=BMW M_LEN3=204 M_MAK4=Buick M_LEN4=207 M_MAK5=Cadillac M_LEN5=221 …

複数行の結果を出す proc sql noprint; select distinct make,max(length) into:m_mak separated by ',' ,:m_len separated by ',' from sashelp.cars group by 1 ; quit; %put &=m_mak &=m_len ; M_MAK=Acura,Audi,BMW,Buick,C… M_LEN=197,204,204,207,221,…

複数行の結果を出す proc sql noprint; select distinct make,max(length) into:m_mak separated by ',' ,:m_len separated by ',' from sashelp.cars group by 1 ; quit; %put &=m_mak &=m_len ; 指定した文字で区切れます M_MAK=Acura,Audi,BMW,Buick,C… M_LEN=197,204,204,207,221,…

閑話休題 SQL intoで作成したマクロ変数のスコープは？ →sas社[SQLプロシジャ内で、INTO句はSYMPUT ルーチンと同様の役割を実行します。] 検証…

閑話休題 %global m_sex; %macro aa(); proc sql noprint; select distinct sex,max(age) into:m_sex,:m_age from sashelp.class where sex='男子' group by 1 ; quit; %mend aa; %aa; %put &=m_sex; %put &=m_age; M_SEX=男子 WARNING: M_AGEのシンボリック参照を解決 できません。

閑話休題 %global m_sex; %macro aa(); proc sql noprint; select distinct sex,max(age) into:m_sex,:m_age from sashelp.class where sex='男子' group by 1 ; quit; %mend aa; %aa; %put &=m_sex; %put &=m_age; 確かに同様っぽい M_SEX=男子 WARNING: M_AGEのシンボリック参照を解決 できません。

集合演算子 • tableとtableを比較し，その結果を出力する • UNION • EXCEPT • INTERSECT

Union • 両方のクエリから重複しない行を 作成する SAS SQLの上だと単純な縦結合

Except • Aのクエリにのみ存在する行を 作成する Datastepのmergeでやる作業を一撃で． data C; merge A B; by key; if A and ^B; run;

Intersect • AとBのクエリに両方に存在する行を 作成する Datastepのmergeでやる作業を一撃で． data C; merge A B; by key; if A and B; run;

どんなときに使う？ • Group byを用いた結果同士を 結合する時など・・・ SOC/PTの集計 SOC,PTをそれぞれGROUP BYで集計後， Union allで結合

proc sql; create table SOC_PT as select distinct AESOCCD ,"" as AEPTCD ,count(distinct USUBJID) as SOC_cnt ,count(distinct USUBJID)/&big_N*100. as SOC_per from adam.adae group by 1 where SAFFL="Y" union all select distinct AESOCCD ,AEPTCD ,count(distinct USUBJID) as PT_cnt ,count(distinct USUBJID)/&big_N*100. as PT_per from adam.adae group by 1,2 where SAFFL="Y" ; quit; SOC PT

一発で．

参考・引用 • SAS 9.3 SQLプロシジャユーザーガイド • https://www.sas.com/offices/asiapacific/ja pan/service/help/pdf/sqlproc.pdf • データステップ100万回 SAS新手一生 • SAS忘備録 http://sasboubi.blogspot.com/2015/01/sql14union.html

• https://www.sas.com/offices/asiapacific/japan/service/help/pdf/sqlproc.pdf
• http://sas-boubi.blogspot.com/2015/01/sql14union.html