SAS SQL

770 Views

April 03, 24

スライド概要

[第5回大阪sas勉強会] 山野辺浩己

profile-image

SAS言語を中心として,解析業務担当者・プログラマなのコミュニティを活性化したいです

シェア

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

関連スライド

各ページのテキスト
1.

SAS SQL EPS SA1 山野辺

2.

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

3.

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

4.

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

5.

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

6.

どんなときに使う? 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;

7.

どんなときに使う? 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;

8.

どんなときに使う? 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を使って ください.

9.

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

10.

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

11.

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

12.

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

13.

複数項目の変数を出す 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

14.

複数行の結果を出す 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

15.

複数行の結果を出す 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 …

16.

複数行の結果を出す 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 …

17.

複数行の結果を出す 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 …

18.

複数行の結果を出す 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,…

19.

複数行の結果を出す 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,…

20.

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

21.

閑話休題 %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のシンボリック参照を解決 できません。

22.

閑話休題 %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のシンボリック参照を解決 できません。

23.

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

24.

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

25.

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

26.

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

27.

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

28.

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

29.

一発で.

30.

参考・引用 • 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