1.2K Views
April 03, 24
スライド概要
[第5回大阪sas勉強会] 山野辺浩己
SAS言語を中心として,解析業務担当者・プログラマなのコミュニティを活性化したいです
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