给你个sql的比较傻的方法,好的方法我自己留着,呵呵。
proc sql noprint;
create table table_n as
select time,id,code,count(distinct sd) as n from a group by time,id,code;
create table table_b as
select t1.time,t1.id,t1.code as code_s,t2.code as code_e
from table_n t1,table_n t2
where t1.time=t2.time and t1.id=t2.id and t1.code<t2.code;
create table table_c as
select t1.time,t1.id,t1.code_s,t1.code_e,t2.sd
from table_b t1 left join a t2
on t1.time=t2.time and t1.id=t2.id and (t2.code=t1.code_s or t2.code=t1.code_e);
create table table_m as
select time,id,code_s,code_e,count(distinct sd) as m label='总个数' from table_c group by time,id,code_s,code_e;
create table table_m as
select t1.*,t2.n as n1
from table_m t1 left join table_n t2
on t1.time=t2.time and t1.id=t2.id and t1.code_s=t2.code;
create table table_m as
select t1.*,t2.n as n2,(t2.n+t1.n1-t1.m)/t1.m as chl label='重合率'
from table_m t1 left join table_n t2
on t1.time=t2.time and t1.id=t2.id and t1.code_e=t2.code;
create table table_clp as
select time,id,code_s,code_e,avg(chl) as clp
from table_m
group by time,id,code_s,code_e;
quit;
|