🐳

SAS(PROC SQL)による100本ノック構造化データ加工編

2022/07/06に公開約33,200字

はじめに

本記事ではデータサイエンス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

ログインするとコメントできます