🐳
SAS(PROC SQL)による100本ノック構造化データ加工編
はじめに
本記事ではデータサイエンス100本ノック（構造化データ加工編）をSAS9.4（主にPROC SQL）で取り組んだプログラムを紹介する。解説はつけておらず、あくまでプログラム例として参考にしていただければ幸いである。また92番以降の正規化、非正規化、データの入出力については今回は解いていない。
データサイエンス100本ノック（構造化データ加工編）の詳細については以下のGithubをご参照ください。
本記事内で、「できない」「わからない」等の表現がある部分については私自身が15分から30分程度調べて見つけられなかっただけで、実装ができる場合もあると思われる。もしご存じの方がいればご教示いただきたい。
(※そもそもproc sqlの練習のためにはじめて触って書いただけなので、よりよい書き方はいくらでもあると思います。)
SQLの基本的なことについては特に記載していないが、私は以下のUdemy講座で学習した。
100本ノック構造化データ加工編のプログラム
データセットのライブラリ
今回、問題を特にあたり利用するデータセットはすべてdataという名前のライブラリに保存した状態で始めている。問題を解くために作成したデータセット等はworkライブラリを用いている。
001
proc sql では limit が使えない代わりに、outobs = n で出力する件数を指定できる。
proc sql outobs=10;
select *
from data.receipt;
quit ;
002
出力したい変数の順に、selectで指定すればよい。
proc sql outobs=10;
select
sales_ymd,
customer_id,
product_cd,
amount
from data.receipt;
quit;
003
変数名を返る場合には、asを利用する。
proc sql outobs=10;
select
sales_ymd as sales_date,
customer_id,
product_cd,
amount
from data.receipt;
quit;
004
データセットから抽出するときの条件はwhere句で指定すればよい。
集計結果に関する条件はwhere句では行えないので注意。
proc sql ;
select
sales_ymd,
customer_id,
product_cd,
amount
from data.receipt
where customer_id = "CS018205000001";
quit;
005
複数条件であれば、where句でandもしくはorで記述する。
proc sql ;
select
sales_ymd,
customer_id,
product_cd,
amount
from data.receipt
where
customer_id = "CS018205000001" and
amount >= 1000;
quit;
006
同様にwhere句で記述する。
proc sql ;
select
sales_ymd,
customer_id,
product_cd,
quantity,
amount
from data.receipt
where
customer_id = "CS018205000001" and
(amount >= 1000 or quantity >=5) ;
quit;
007
同様にwhere句で記述する。
proc sql ;
select
sales_ymd,
customer_id,
product_cd,
amount
from data.receipt
where
customer_id = "CS018205000001" and
amount between 1000 and 2000;
quit;
008
同様にwhere句で記述する。
proc sql ;
select
sales_ymd,
customer_id,
product_cd,
amount
from data.receipt
where
customer_id = "CS018205000001" and
product_cd ^= "P071401019";
quit ;
009
条件式全体にNOTをつけることもできる。
proc sql;
SELECT *
FROM data.store
WHERE NOT (prefecture_cd = 13 OR floor_area > 900) ;
quit;
010
substr(string, position, length)で特定の位置からlengthだけの文字列を取得できる。
proc sql outobs = 10;
select *
from data.store
where substr(store_cd, 1,3) = "S14";
quit;
011
substrを用いるが、末尾が欲しいため引数にlength関数を用いた。
proc sql outobs = 10;
select *
from data.customer
where substr(customer_id, length(customer_id) , 1) = "1";
quit;
012
find関数はある文字列に特定の文字列が見つからなければ0を返すので、それを利用した。
もしくはcontainsを使ってもできる。
* 解1 ;
proc sql ;
select *
from data.store
where find (address, "横浜市") ^= 0 ;
quit;
* 解2 ;
proc sql ;
select *
from data.store
where address contains "横浜市" ;
quit;
013
AからFのいずれかに合致していればよいので、in を利用した。
proc sql outobs = 10 ;
select *
from data.customer
where substr(status_cd, 1,1) in ("A","B","C","D","E","F")
;
quit;
014
同様に1から9のいずれかに合致していればよいので、in を利用した。
proc sql outobs = 10 ;
select *
from data.customer
where substr(status_cd, length(status_cd),1) in ("1","2","3","4","5","6","7","8","9")
;
quit;
015
14,15で用いた条件をandで繋げばよい。
proc sql outobs = 10 ;
select *
from data.customer
where
substr(status_cd, 1,1) in ("A","B","C","D","E","F") and
substr(status_cd, length(status_cd),1) in ("1","2","3","4","5","6","7","8","9")
;
quit;
016
where句でlikeを利用した。_ は任意の一文字を表す。
また、それぞれが3桁あるいは4桁の数字になっているかはSCANで抽出して確認した。
likeに関しては下記が参考になる。
scanに関しては下記が参考になる。
proc sql ;
select *
from data.store
where
tel_no like '___-___-____'
and scan( tel_no , 1 , '-' ) between '000' and '999'
and scan( tel_no , 2 , '-' ) between '000' and '999'
and scan( tel_no , 3 , '-' ) between '0000' and '9999'
;
quit;
017
order by句でascで昇順、descで降順に並べ替えができる。
誕生日で昇順にしている。
proc sql outobs=10;
select *
from data.customer
order by birth_day asc;
quit;
018
同様に誕生日で降順にしている。
proc sql outobs=10;
select *
from data.customer
order by birth_day desc;
quit;
019
proc sqlでrank関数は恐らく使えなさそうなので、proc rankを用いた。
amountが大きい順に順位を振りたいのでproc rankでは降順にしている。
またamountが等しい場合に同一順位を与えている（同一の場合の順位の当て方はtiesで指定する）。
proc rank data=data.receipt out=q19 ties=low descending;
var amount;
ranks rank;
run;
proc sql outobs=10;
select
customer_id,
amount,
rank
from work.q19
order by rank asc;
quit;
020
等しい場合に別の順位を与えることが、proc rank のtiesのオプション（HIGH, LOW, MEAN, DENSE）では対処できなさそうだったので、Dataで_N_を利用した。
proc sort data=data.receipt out = q20;
by descending amount;
run;
data q20;
set q20;
rank = _N_ ;
run;
proc sql outobs=10;
select
customer_id,
amount,
rank
from q20
order by rank asc;
quit;
021
単に数えるのであれば、countを利用する。
proc sql;
select
count(*)
from data.receipt;
quit;
022
distinct でユニークな件数をカウントする。
proc sql;
select
count(distinct customer_id) as no_of_customer
from data.receipt;
quit;
023
group by でstore_cdを指定する。
proc sql ;
select
store_cd,
sum(amount) as total_amount,
sum(quantity) as total_quantity
from data.receipt
group by 1
order by 1 asc;
quit;
024
select句のなかでformatを指定することができる。
proc sql outobs=10;
select
customer_id,
max(sales_ymd) as newest_date format=yymmdd10.
from data.receipt
group by 1
order by 1 asc;
quit;
025
同様にformatを指定する。
proc sql outobs=10;
select
customer_id,
min(sales_ymd) as oldest_date format=yymmdd10.
from data.receipt
group by 1
order by 1 asc;
quit;
026
集計した結果に対する条件はhaving句で行う。
proc sql outobs=10;
select
customer_id,
max(sales_ymd) as newest_date format=yymmdd10. ,
min(sales_ymd) as oldest_date format=yymmdd10.
from data.receipt
group by 1
having max(sales_ymd) ^= min(sales_ymd)
order by 1 asc;
quit;
027
avgで平均を取得することができ、平均値で降順に並べ替えている。
proc sql outobs = 5;
select
store_cd,
avg(amount) as avg_amount
from data.receipt
group by 1
order by 2 desc;
quit;
028
中央値はmedianで算出できる。
proc sql outobs = 5;
select
store_cd,
median(amount) as p50_amount
from data.receipt
group by 1
order by 2 desc;
quit;
029
最頻値を取得する方法がわからなかったので、いくつかに分けて実装した。
まずcountで集計後に店舗ごとにランクを付けて、ランクが1の値（最頻値）を取得した。
proc sql ;
create table q29 as
select
store_cd,
product_cd,
count(product_cd) as count_product_cd
from data.receipt
group by 1, 2
order by 1, 2;
quit;
proc rank data=q29 out=q29 ties=low descending;
var count_product_cd;
by store_cd;
ranks rank;
run;
proc sql outobs=10 ;
select
store_cd,
product_cd,
count_product_cd
from q29
where rank = 1
order by 1 asc;
quit;
030
varを利用すると不偏分散になってしまうので、標本分散になるように調整した。
proc sql outobs = 5;
select
store_cd,
(VAR(amount) * (count(amount) - 1)) / count(amount) as var_amount
from data.receipt
group by 1
order by 2 desc;
quit;
031
同様に不偏分散をもとに標本での標準偏差を算出した。
proc sql outobs = 5;
select
store_cd,
sqrt((VAR(amount) * (count(amount) - 1)) / count(amount)) as std_amount
from data.receipt
group by 1
order by 2 desc;
quit;
032
パーセンタイル点をproc sqlで算出する方法がわからなかったので、proc meansを利用した。
proc means data=data.receipt min p25 p50 p75 max ;
var amount ;
ods output summary = q32;
run;
proc sql;
select *
from q32;
quit;
033
集計結果に対する条件なのでhavingを利用した。
proc sql ;
select
store_cd,
avg(amount) as avg_amount
from data.receipt
group by 1
having avg_amount >= 330
order by 1;
quit;
034
顧客IDごとの合計を平均した値を算出したいので、サブクエリで先に顧客ごとの合計を算出してから平均値を算出した。
proc sql;
select
avg(amount_by_cutomer) as avg_amount_user
from
(select
customer_id,
sum(amount) as amount_by_cutomer
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1 )
;
quit;
035
同様に、サブクエリで先に顧客ごとの合計を算出した。
proc sql outobs=10;
select *
from
(select
customer_id,
sum(amount) as amount_by_cutomer
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1 )
having amount_by_cutomer >= avg(amount_by_cutomer)
;
quit;
036
内部結合を行うため、inner joinを行った。
proc sql outobs = 10 ;
select
r.*,
s.store_name
from
data.receipt as r
inner join data.store as s
on r.store_cd = s.store_cd
;
quit;
037
同様に、inner joinを行った。
proc sql outobs = 10 ;
select
p.*,
c.category_small_name
from
data.product as p
inner join data.category as c
on p.category_small_cd = c.category_small_cd
;
quit;
038
欠損値に0を埋めたいので、COALESCEを用いた。
COALESCEは、COALESCE(list)でlistのいちばん初めの非欠損値を返す。
もしlistがすべて欠損であれば欠損を返す。
そのため、r.amountが欠損の場合に0を返すようになっている。
proc sql outobs = 10 ;
select
c.customer_id,
COALESCE(sum(r.amount), 0) as ttl_amount
from
data.customer as c
left outer join data.receipt as r
on c.customer_id = r.customer_id
where substr(c.customer_id, 1,1) ^ = "Z" and c.gender_cd = 1
group by 1
;
quit;
039
proc sql では create table xx as で xx という新しいデータセットを作成できる。
outer joinをするとcustomer_idが2列になってしまうので、COALESCEを用いて1つ目もしくは2つ目のデータセットに含まれるcustomer_idを新たなcustomer_idとして定義しなおしている。
proc sql outobs = 20;
create table q39_1 as
select customer_id , count(distinct sales_ymd) as days
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1
order by 2 desc
;
quit;
proc sql outobs = 20;
create table q39_2 as
select customer_id , sum(amount) as ttl_amount
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1
order by 2 desc
;
quit;
proc sql ;
select
COALESCE(q1.customer_id, q2.customer_id) as customer_id,
q1.days,
q2.ttl_amount
from q39_1 as q1
full outer join q39_2 as q2
on q1.customer_id = q2.customer_id
;
quit;
040
cross joinでクロス結合することができる。
proc sql ;
select count(1)
from data.product
cross join data.store
;
quit;
041
proc sqlでlag関数を利用する方法がわからなかったので、dataを利用した。
proc sql;
create table q41 as
select
sales_ymd,
sum(amount) as ttl_amount
from data.receipt
group by sales_ymd
order by sales_ymd asc
;
quit;
data q41;
set q41;
lag_amount = lag(ttl_amount);
diff_amount = ttl_amount - lag_amount;
run;
proc sql;
select *
from q41
order by sales_ymd asc
;
quit;
042
同様に、lag関数を用いる部分はdataで行った。
proc sql;
create table q42 as
select
sales_ymd,
sum(amount) as ttl_amount
from data.receipt
group by sales_ymd
order by sales_ymd asc
;
quit;
data q42;
set q42;
lag_1 = lag(sales_ymd);
lag_amount_1 = lag(ttl_amount);
lag_2 = lag2(sales_ymd);
lag_amount_2 = lag2(ttl_amount);
lag_3 = lag3(sales_ymd);
lag_amount_3 = lag3(ttl_amount);
format lag_1 yymmdd10. lag_2 yymmdd10. lag_3 yymmdd10.;
run;
proc sql;
select *
from q42
order by sales_ymd asc
;
quit;
043
"case 変数 when 条件 then （条件に一致したときの）戻り値 else （それ以外のときの）戻り値"を用いた。つまり、 "case c.gender_cd when 0 then r.amount else 0 end" でgenderが0のときはamountを、そうではないときは0を返すという意味になっている。
proc sql ;
select
int(c.age/10)-1 as age_category,
sum(case c.gender_cd when 0 then r.amount else 0 end) as male,
sum(case c.gender_cd when 1 then r.amount else 0 end) as female,
sum(case c.gender_cd when 9 then r.amount else 0 end) as unknown
from data.receipt as r
left join data.customer as c
on r.customer_id = c.customer_id
where c.age is not null
group by 1
;
quit;
044
先ほどとは異なるcase whenの書き方を用いた。
もしくはunionで縦結合させてもよい。unionで重複を除き縦結合、union allで重複もそのままで縦結合できる。
* 解1 ;
proc sql ;
select
int(c.age/10) -1 as age_category,
case
when c.gender_cd = 0 then "00"
when c.gender_cd = 1 then "01"
when c.gender_cd = 9 then "99"
end as gender,
sum(r.amount) as ttl_amount
from data.receipt as r
left join data.customer as c
on r.customer_id = c.customer_id
where c.gender_cd in (0,1,9) and c.age is not null
group by 1, 2
order by 2
;
quit;
* 解2 ;
proc sql ;
create table q44 as
select
int(c.age/10)-1 as age_category,
sum(case c.gender_cd when 0 then r.amount else 0 end) as male,
sum(case c.gender_cd when 1 then r.amount else 0 end) as female,
sum(case c.gender_cd when 9 then r.amount else 0 end) as unknown
from data.receipt as r
left join data.customer as c
on r.customer_id = c.customer_id
where c.age is not null
group by 1
;
quit;
proc sql ;
SELECT age_category, '00' AS gender_cd , male AS ttl_amount FROM q44
UNION
SELECT age_category, '01' AS gender_cd , female AS ttl_amount FROM q44
UNION
SELECT age_category, '99' AS gender_cd , unknown AS ttl_amount FROM q44
order by 2
;
quit;
045
putで型変換すればよい。
proc sql outobs = 10 ;
select
customer_id,
put(birth_day, yymmddn8.) as birthday_char
from data.customer
;
quit;
046
データの読み込み時に以下のプログラムで事前に処理してしまったので割愛。
data data.customer;
set data.customer;
application_date = input(put(application_date,8.), yymmdd8.);
format application_date yymmdd10.;
run;
data data.receipt;
set data.receipt;
sales_ymd = input(put(sales_ymd, 8.), yymmdd8.);
format sales_ymd yymmdd10.;
run;
047
同様にデータの読み込み時に処理してしまったので割愛。
048
UNIX秒とSASで基準日時が異なるため、そこだけ調整する必要がある。
またSAS日時値から日付値にするときはdatepartを用いる。
data _null_;
unix='01jan1970:00:00:00am'dt;
sas='01jan1960:00:00:00am'dt;
call symputx("s", unix-sas);
run;
proc sql outobs = 10 ;
select
receipt_no,
receipt_sub_no,
DATEPART(sales_epoch + &s.) format=yymmdd10. as date
from data.receipt
;
quit;
049
SAS日付値からyearで年だけをとりだす。
proc sql outobs = 10 ;
select
receipt_no,
receipt_sub_no,
year(DATEPART(sales_epoch + &s.)) as year
from data.receipt
;
quit;
050
数値を0づめするところは下記のサイトが参考になる。
proc sql outobs = 10 ;
select
receipt_no,
receipt_sub_no,
put(month(DATEPART(sales_epoch + &s.)), z2.) as month
from data.receipt
;
quit;
同様の操作を文字列にする場合には、tranwrdが使える。
proc sql outobs = 10 ;
select
receipt_no,
receipt_sub_no,
tranwrd(put(put(DATEPART(sales_epoch + &s.), month.), $2.-R)," ","0") as month
from data.receipt
;
quit;
051
同様に、数値を0づめする。
proc sql outobs = 10 ;
select
receipt_no,
receipt_sub_no,
put(day(DATEPART(sales_epoch + &s.)), z2.) as day
from data.receipt
;
quit;
052
case when を用いて、2値の変数を作成した。
proc sql outobs = 10 ;
select
customer_id,
sum(amount) as ttl_amount,
case
when sum(amount) <= 2000 then 0
else 1
end as ttl_amount_c
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by customer_id
;
quit;
053
サブクエリで東京をあらわす2値変数をつくり、それごとにcountした。
proc sql ;
select
c.f_tokyo,
count(distinct r.customer_id)
from data.receipt as r
left join
(select
customer_id,
case
when 100 <=input(substr(postal_cd,1,3), 4. ) <= 209 then 1
else 0
end as f_tokyo
from data.customer) as c
on r.customer_id = c.customer_id
where c.f_tokyo is not null
group by 1;
;
quit;
054
分類が高々4つなので、case when で条件分岐させた。
proc sql outobs = 10 ;
select
customer_id,
address,
case
when substr(address,1,6) = "埼玉県" then 11
when substr(address,1,6) = "千葉県" then 12
when substr(address,1,6) = "東京都" then 13
when substr(address,1,8) = "神奈川県" then 14
end as address_code
from data.customer
;
quit;
055
パーセンタイル点を算出する方法がわからないので、proc meansを用いた。
結果の結合はcross joinで行った。
proc sql ;
create table q55 as
select
customer_id,
sum(amount) as ttl_amount
from data.receipt
group by 1
;
quit;
proc means data=q55 min p25 p50 p75 max ;
var ttl_amount ;
ods output summary = q55_percentile;
run;
proc sql outobs = 10;
select
q.customer_id,
q.ttl_amount,
case
when q.ttl_amount < p.ttl_amount_p25 then 1
when q.ttl_amount < p.ttl_amount_p50 then 2
when q.ttl_amount < p.ttl_amount_p75 then 3
else 4
end as ttl_amount_c
from q55 as q
cross join (select * from q55_percentile) as p
;
quit;
056
60歳以上だけ別処理にする点に注意する。
proc sql outobs = 10 ;
select
customer_id ,
birth_day,
age,
case
when age < 60 then int(age/10)
else 6
end as age_c
from data.customer
;
quit;
057
文字列の結合はcatを利用した。
* 057;
proc sql outobs = 10;
select
q1.customer_id,
cat(put(10 * q2.age_c, best2.), "-", put(q1.gender_cd, best1.)) as age_gender_c
from
data.customer as q1
inner join
(select
customer_id,
case
when age < 60 then int(age/10)
else 6
end as age_c
from data.customer) as q2
on q1.customer_id = q2.customer_id
;
quit;
058
case when で各性別ごとに1になるダミー変数を作った。
proc sql outobs = 10 ;
select
customer_id,
gender_cd,
case gender_cd when 0 then 1 else 0 end as male,
case gender_cd when 1 then 1 else 0 end as female,
case gender_cd when 9 then 1 else 0 end as unknown
from data.customer
;
quit;
059
サブクエリで顧客IDごとの合計をだしてから、標準化した。
proc sql outobs = 10 ;
select
customer_id ,
ttl_amount,
(ttl_amount - avg(ttl_amount)) /sqrt(var(ttl_amount)) as z_ttl_amount
from
(select customer_id , sum(amount) as ttl_amount
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1)
;
quit;
060
同様に、サブクエリで顧客IDごとの合計をだしてから正規化した。
proc sql outobs = 10 ;
select
customer_id ,
ttl_amount,
(ttl_amount - min(ttl_amount)) /(max(ttl_amount)-min(ttl_amount)) as z_ttl_amount
from
(select customer_id , sum(amount) as ttl_amount
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1)
;
quit;
061
常用対数はlog10で求められる。
proc sql outobs = 10 ;
select
customer_id ,
sum(amount) as ttl_amount,
log10(sum(amount)) as log10_ttl_amount
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1
;
quit;
062
自然対数はlogで求められる。
proc sql outobs = 10 ;
select
customer_id ,
sum(amount) as ttl_amount,
log(sum(amount)) as log_e_ttl_amount
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1
;
quit;
063
単に引き算をして、diffとして定義した。
proc sql outobs = 10 ;
select
product_cd,
unit_price,
unit_cost,
unit_price - unit_cost as diff
from data.product
;
quit;
064
利益率を計算してからavgで平均を求める。
proc sql outobs = 10 ;
select
avg((unit_price - unit_cost)/unit_price) as avg_rate
from data.product
;
quit;
065
1円未満を切り捨てすることに注意する。
proc sql outobs = 10 ;
select
product_cd,
int(unit_cost / 0.7) as new_price,
(int(unit_cost / 0.7) - unit_cost)/int(unit_cost / 0.7) as new_rate
from data.product
;
quit;
066
1円未満を四捨五入する（もしくは偶数へ丸める）ことに注意する。
proc sql outobs = 10 ;
select
product_cd,
round(unit_cost / 0.7) as new_price,
(round(unit_cost / 0.7) - unit_cost)/round(unit_cost / 0.7) as new_rate
from data.product
;
quit;
067
1円未満を切り上げることに注意する。
proc sql outobs = 10 ;
select
product_cd,
ceil(unit_cost / 0.7) as new_price,
(ceil(unit_cost / 0.7) - unit_cost)/ceil(unit_cost / 0.7) as new_rate
from data.product
;
quit;
068
問題文どおりに出力する。
proc sql outobs = 10 ;
select
product_cd,
unit_price,
int(unit_price * 1.1) as price_with_tax
from data.product
;
quit;
069
顧客ごとの合計と、顧客ごとのカテゴリ07の合計を算出したいため、顧客ごとの合計をサブクエリで先に計算してから結合させた。
proc sql outobs = 10 ;
select
r.customer_id,
sum(r.amount) as amount_07,
t.ttl_amount as all,
sum(r.amount) / t.ttl_amount as proportion
from
data.receipt as r
left join
(select
customer_id,
sum(amount) as ttl_amount
from data.receipt
group by 1 ) as t
on r.customer_id = t.customer_id
left join
data.product as p
on r.product_cd = p.product_cd
where p.category_major_cd = 7
group by 1
;
quit;
070
日付の差を計算して、diffとして定義した。
proc sql outobs = 10 ;
select
r.customer_id,
r.sales_ymd,
c.application_date,
r.sales_ymd - c.application_date as diff
from
(select distinct
customer_id, sales_ymd
from data.receipt) as r
left join data.customer as c
on r.customer_id = c.customer_id
order by customer_id asc , diff asc
;
quit;
071
intckだと切り捨てる部分が思った通りの挙動をしてくれないので、若干煩雑だが自分で処理を書いた。
dayで切り捨てるべきかどうかが変わるため、そこで条件分岐させる。
proc sql outobs = 10 ;
select
r.customer_id,
r.sales_ymd,
c.application_date,
case when
day(r.sales_ymd) < day(c.application_date) then
12 * (year(r.sales_ymd) - year(c.application_date)) +
month(r.sales_ymd) - month(c.application_date) -1
else
12 * (year(r.sales_ymd) - year(c.application_date)) +
month(r.sales_ymd) - month(c.application_date)
end as diff_month
from
(select distinct
customer_id, sales_ymd
from data.receipt) as r
left join data.customer as c
on r.customer_id = c.customer_id
having customer_id = "CS008515000005"
order by customer_id asc
;
quit;
* intck だと結果が合わない ;
proc sql outobs = 10 ;
select
r.customer_id,
r.sales_ymd,
c.application_date,
intck("month", c.application_date, r.sales_ymd) as diff_month
from
(select distinct
customer_id, sales_ymd
from data.receipt) as r
left join data.customer as c
on r.customer_id = c.customer_id
having customer_id = "CS008515000005"
order by customer_id asc
;
quit;
072
同様だが、より複雑にはなっている。
monthだけで判断が可能な部分と、dayの情報も必要な部分があるためそこに注意しながら条件分岐を行う。
proc sql outobs = 10 ;
select
r.customer_id,
r.sales_ymd,
c.application_date,
case when
month(r.sales_ymd) < month(c.application_date) then
year(r.sales_ymd) - year(c.application_date) -1
when (month(r.sales_ymd) = month(c.application_date) and day(r.sales_ymd) < day(c.application_date)) then
year(r.sales_ymd) - year(c.application_date) -1
else
year(r.sales_ymd) - year(c.application_date)
end as diff_year
from
(select distinct
customer_id, sales_ymd
from data.receipt) as r
left join data.customer as c
on r.customer_id = c.customer_id
order by customer_id asc
;
quit;
073
秒数にするために日数に$ 246060 $ をかけている。
より丁寧にするのであれば、うるう秒を調整した日付をまたいでいるかを判断する処理を加えるべきだが割愛した。
proc sql outobs = 10 ;
select
r.customer_id,
r.sales_ymd,
c.application_date,
24 * 60 * 60 * (r.sales_ymd - c.application_date) as diff_seconds
from
(select distinct
customer_id, sales_ymd
from data.receipt) as r
left join data.customer as c
on r.customer_id = c.customer_id
order by customer_id asc
;
quit;
074
曜日の判定にはweekdayを利用する。
weekdayは日曜日であれば1、月曜日であれば2、…を返す。
proc sql outobs = 10 ;
select
customer_id,
sales_ymd,
sales_ymd - mod(weekday(sales_ymd)+5, 7) as day_monday format=yymmdd10.,
mod(weekday(sales_ymd)+5, 7) as diff
from data.receipt
order by customer_id asc , diff asc
;
quit;
075
抽出数がずれても良いのであれば以下のコードでよい。
proc sql;
create table q75_1 as
select *
from data.customer
where ranuni(1234) <= 0.010
;
quit;
もし抽出数を固定するのであれば以下のコードを利用する。
またselectでintoを使うことで、マクロ変数として格納できる。
下記が参考になる。
proc sql ;
select ceil(count(distinct customer_id) * 0.010)
into :no
from data.customer
;
quit;
proc sql outobs = &no. ;
create table q75_2 as
select *
from data.customer
order by ranuni(1234)
;
quit;
076
あまりよい方法が思い浮かばず、UNIONで無理やり層別に抽出して結合した。
proc sql ;
create table q75_1 as
select *
from data.customer
where gender_cd = 0 and ranuni(1111) <= 0.10
union all
select *
from data.customer
where gender_cd = 1 and ranuni(2222) <= 0.10
union all
select *
from data.customer
where gender_cd = 9 and ranuni(9999) <= 0.10
;
quit;
SASでの抽出であれば、基本的にはsurvey selectを使う気がする。
proc sort data=data.customer out = customer;
by gender_cd;
run;
proc surveyselect data=customer out = q75_2
seed=1234
samprate=0.1;
strata gender_cd / alloc=prop;
run;
077
計算結果に対する条件付けなのでhavingで行った。
proc sql ;
select
customer_id,
exp(log_ttl_amount) as ttl_amount,
log_ttl_amount,
avg(log_ttl_amount) as mean,
sqrt((VAR(log_ttl_amount) * (count(log_ttl_amount) - 1)) / count(log_ttl_amount)) as std
from
(select customer_id ,log(sum(amount)) as log_ttl_amount
from data.receipt
group by 1)
having log_ttl_amount > mean + 3 * std or log_ttl_amount < mean - 3 * std
;
quit;
078
パーセンタイル点をだすためにproc meansを利用した。
proc sql ;
create table q78 as
select
customer_id,
sum(amount) as ttl_amount
from data.receipt
where substr(customer_id, 1,1) ^ = "Z"
group by 1
;
quit;
proc means data=q78 min p25 p50 p75 max ;
var ttl_amount;
ods output summary = q78_2;
run;
proc sql outobs=10;
select customer_id, ttl_amount
from q78
cross join q78_2
where
ttl_amount < ttl_amount_p25 - (ttl_amount_p75 - ttl_amount_p25) or
ttl_amount > ttl_amount_p75 + (ttl_amount_p75 - ttl_amount_p25)
;
quit;
079
proc contents で変数名をまとめて取得して、nmiss(var1), nmiss(var2),となるような文字列をマクロ変数に格納させている。そのマクロ変数をselectで指定すれば全変数にnmissを行うことになる。
proc contents data = data.product ;
ods output variables = q79 ;
run;
proc sql ;
select cat("nmiss(", Variable, ")", " as ", Variable) as temp
into :query separated by ', '
from work.q79
;
quit;
proc sql outobs=10;
select &query.
from data.product
;
quit;
今後も欠損を数えることがあるため、missing_checkとして保存する。
%macro missing_check(data);
ods select none;
proc contents data = &data.;
ods output variables = temp ;
run;
proc sql ;
select cat("nmiss(", Variable, ")", " as ", Variable)
into :query separated by ', '
from temp
;
quit;
ods select default;
proc sql ;
select &query.
from &data.
;
quit;
%mend;
080
cmissで各行の欠損値の数を数えさせて、それが0の行を抽出している。
全変数を書くのは大変なので先ほどと同様にproc contentsを用いた。
proc contents data = data.product ;
ods output variables = q80 ;
run;
proc sql ;
select variable
into :list separated by ', '
from work.q80
;
quit;
proc sql ;
create table q80_2 as
select *
from data.product
where cmiss(&list.) < 1
;
quit;
* check ;
proc sql;
select count(*)
from data.product
;
quit;
proc sql;
select count(*)
from q80_2
;
quit;
081
欠損値に代入するために、case whenを利用した。
proc sql ;
create table q81 as
select
product_cd,
unit_price,
unit_cost,
case unit_price when . then round(avg(unit_price)) else unit_price end as unit_price_impute,
case unit_cost when . then round(avg(unit_cost)) else unit_cost end as unit_cost_impute
from data.product
;
quit;
* check ;
%missing_check(q81) ;
082
補完方法が異なるだけで、同様に行える。
proc sql ;
create table q82 as
select
product_cd,
unit_price,
unit_cost,
case unit_price when . then round(median(unit_price)) else unit_price end as unit_price_impute,
case unit_cost when . then round(median(unit_cost)) else unit_cost end as unit_cost_impute
from data.product
;
quit;
* check ;
%missing_check(q82) ;
083
カテゴリごとの中央値を算出するためにサブクエリを利用した。
proc sql ;
create table q83 as
select
product_cd,
p.category_small_cd,
unit_price,
unit_cost,
case unit_price when . then median_price else unit_price end as unit_price_impute,
case unit_cost when . then median_cost else unit_cost end as unit_cost_impute
from
data.product as p
left join
(select
category_small_cd,
round(median(unit_price)) as median_price,
round(median(unit_cost)) as median_cost
from data.product
group by 1 ) as m
on p.category_small_cd = m.category_small_cd
;
quit;
084
全期間の合計、2019年の合計をそれぞれサブクエリで算出したうえで、売上実績がない場合の処理をした。
proc sql ;
create table q84 as
select
c.customer_id,
case r.amount_2019 when . then 0 else r.amount_2019 end as sales_2019,
case t.ttl_amount when . then 0 else t.ttl_amount end as ttl_sales,
case
when t.ttl_amount = . then 0
when r.amount_2019 = . then 0
else r.amount_2019 / t.ttl_amount
end as proportion_2019
from
data.customer as c
left join
(select
customer_id,
sum(amount) as amount_2019
from data.receipt
where year(sales_ymd) = 2019
group by 1) as r
on c.customer_id = r.customer_id
left join
(select
customer_id,
sum(amount) as ttl_amount
from data.receipt
group by 1) as t
on c.customer_id = t.customer_id
having proportion_2019 > 0
;
quit;
* check ;
%missing_check(q84) ;
085
問題文どおりに、緯度と経度の平均値をとればよい。
proc sql ;
create table q85 as
select c.* , g.avg_longitude, g.avg_latitude
from data.customer as c
left join
(select
postal_cd,
avg(longitude) as avg_longitude,
avg(latitude) as avg_latitude
from data.geocode
group by 1 ) as g
on c.postal_cd = g.postal_cd
;
proc sql outobs=10;
select *
from q85
;
quit;
086
Degreeからラジアンに直すためにd_to_rというマクロ変数を用意した。
あとは、問題に書かれている式を丁寧に写した。
data _null_;
d_to_r =constant('pi')/180;
call symputx("d_to_r",d_to_r);
run;
proc sql outobs = 10 ;
select
c.customer_id,
c.address as customer_address,
s.address as store_address,
6371 * arcos
(sin(&d_to_r. *avg_latitude)*sin(&d_to_r. *latitude) +
cos(&d_to_r. *avg_latitude)*cos(&d_to_r. *latitude)*cos(&d_to_r. * avg_longitude - &d_to_r. * longitude)) as distance_km
from
q85 as c
left join
data.store as s
on c.application_store_cd = s.store_cd
having customer_id = "CS037613000071"
;
quit;
087
重複を削除する方法がわからなかったので、proc sortの nodupkeyを利用した。
proc sql;
create table q87 as
select
c.customer_id, c.customer_name, c.postal_cd, ttl_amount
from
data.customer as c
left join
(select customer_id, sum(amount) as ttl_amount
from data.receipt
group by 1
) as r
on r.customer_id = c.customer_id
order by 2 asc, 3 asc, 4 desc, 1 asc
;
quit;
proc sort data=q87 out=q87_2 nodupkey dupout = q87_3;
by customer_name postal_cd;
run;
088
先ほど作ったデータセットと結合させた。
proc sql ;
create table q88 as
select
q1.customer_name,
q1.postal_cd,
q1.customer_id as old_customer_id,
q2.customer_id as new_customer_id
from
q87 as q1
left join
q87_2 as q2
on q1.customer_name = q2.customer_name and q1.postal_cd = q2.postal_cd
;
quit;
proc sql;
select
count(distinct old_customer_id) as count_old,
count(distinct new_customer_id) as count_new,
count(distinct old_customer_id) - count(distinct new_customer_id) as diff
from q88;
quit;
089
ランダム抽出のときと同様にflagを立てる。
proc sql ;
create table q89 as
select
c.customer_id,
r.ttl_amount,
case
when ranuni(1234) <= 0.20 then 1
else 0
end as test_flag
from
data.customer as c
left join
(select
customer_id,
sum(amount) as ttl_amount
from data.receipt
group by 1) as r
on c.customer_id = r.customer_id
where ttl_amount > 0
;
quit;
proc sql ;
create table q89_study as
select *
from q89
where test_flag = 0
;
create table q89_test as
select *
from q89
where test_flag = 1
;
quit;
090
まず、日付値で計算処理するのが怖いのでyyyymmの形式になるような数値データに直す。
スタート位置を決めるために各行の行番号を振る。
そしてi個目のデータセットを、start行から、学習用にstudy_month、テスト用にtest_monthだけ持ってくる関数を作成した。
今回はstart + study_month + test_month が行数を超える場合のエラー処理は特にしていない。
proc sql ;
create table q90 as
select
year(sales_ymd) * 100 + month(sales_ymd) as num_ym,
sum(amount) as ttl_amount
from data.receipt
group by 1;
quit;
data q90;
set q90;
row = _N_ ;
run;
%macro q90(i, start, study_month, test_month);
proc sql;
create table q90_&i. as
select
num_ym, ttl_amount,
case
when &start. <= row < &start. + &study_month. then 1
when &start. + &study_month. <= row < &start. + &study_month. + &test_month. then 2
else 0 end as flag
from q90
;
create table q90_&i._study as
select *
from q90_&i.
where flag = 1
;
create table q90_&i._test as
select *
from q90_&i.
where flag = 2
;
quit;
%mend;
%q90(1, 1, 12, 6)
%q90(2, 7, 12, 6)
%q90(3, 13, 12, 6)
proc sql ;
select *
from q90_1_study
union all
select *
from q90_1_test
;
quit;
091
人数のぶれをなくしたランダムサンプリングの方法を応用すればよい。
今回は売上実績の有無の人数で少ないほうを事前に確認してからプログラムを書いているが、少ないほうに合わせるプログラムにしたほうがよいかもしれないが今回は書いていない。
proc sql;
create table q91 as
select
c.customer_id, ttl_amount
from
data.customer as c
left join
(select customer_id, sum(amount) as ttl_amount
from data.receipt
group by 1
) as r
on c.customer_id = r.customer_id
;
quit;
proc sql;
create table q91_amount as
select *
from q91
where ttl_amount ^= . ;
;
select count(*) into :n_amount
from q91_amount
;
quit;
proc sql outobs = &n_amount. ;
create table q91_no_amount as
select *
from q91
where ttl_amount = .
order by ranuni(1234)
;
quit;
proc sql ;
select *
from
(select count(*) as no_amount from q91_amount)
cross join
(select count(*) as no_no_amount from q91_no_amount)
;
quit;
092-100 (※解いていない)
正規化、非正規化、データの入出力については今回は解いていない。
まとめ
本記事では100本ノック構造化データ加工編をSAS9.4（主にPROC SQL）で取り組んだプログラムを紹介した。PROC SQLを利用しなければ解けない問題は存在しないが、PROC SQLを用いることで容易に実装できるような問題はいくつかあったように感じた。
特にデータハンドリングをする場合にはうまく活用できれば、より効率的なプログラムを書けるように感じたため今後の引き続きPROC SQLの学習を続けようと思う。
サポートして頂けるとモチベーションに繋がりますのでぜひ宜しくお願いします。データ解析や臨床研究でのご相談があれば、お気軽にTwiiterもしくはメールにてご連絡下さい。
作成者：Masahiro Kondo
作成日：2022/7/6
連絡先：m.kondo1042(at)gmail.com
Discussion