作者svin (呼呼呼)
看板Statistics
标题Re: [程式] sas sql语法
时间Tue Sep 25 13:48:55 2012
我的写法是算出type1, type2, type3後用full join拼在一起
原始资料名称为data,语法为
proc sql;
create table table1 as
select data1.ID, data1.casetype, type1, type2, type3
from
(select data.ID, data.casetype, a.type1, b.type2
from
data,
(select ID, casetype,count(casetype) as type1
from data
where casetype = 1
group by ID, casetype) as a full join
(select ID, casetype,count(casetype) as type2
from data
where casetype = 2
group by ID, casetype) as b on a.ID=b.ID
where data.ID = a.ID) as data1 full join
(select ID, casetype,count(casetype) as type3
from data
where casetype = 3
group by ID, casetype) as c on data1.ID=c.ID;
看起来很多很繁杂, 不过整个过程大概分两个步骤
红色字体内用full join把两个view合并起来, 得到
的data1长这样
ID casetype type1 type2
a 1 2 .
a 1 2 .
b 1 1 1
b 2 1 1
b 3 1 1
再把整个data1和第三个view合并起来, 最後的table1
应该就是你想要的结果了
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 169.231.32.90
※ 编辑: svin 来自: 169.231.32.90 (09/25 13:57)