- data a;
- input id year qtr name $30.;
- cards;
- 1 1996 1 Zhangsan, Lisi, Wangwu
- 1 1996 1 Liuliu
- 1 1996 1 Baba, Zhangsheng
- 1 1996 1 Qiqi
- 1 1996 2 Zhangsan
- 1 1996 2 Lisi, Liuliu
- 1 1996 2 Baba, Qiqi
- 1 1996 2 Wangquan
- 2 1996 1 Baba, Zhangsheng
- 2 1996 1 Qiqi
- 2 1996 2 Zhangsan
- 2 1996 2 Lisi, Liuliu
- ;
- data b;
- input year qtr name $10.;
- cards;
- 1996 1 Zhangsan
- 1996 1 Liuliu
- 1996 1 Baba
- 1996 1 Zhangsheng
- 1996 2 Zhangsan
- 1996 2 Lisi
- ;
- data a;
- set a;
- ii=_n_;
- num=count(name, ",")+1;
- run;
- proc sql;
- create table c as
- select ii, id, year, qtr, name,
- case when sum(ind)=num then 1 else 0 end as var1,
- max(ind) as var2 from
- (
- select a.*, b.name as name1,
- case when index(a.name, strip(b.name)) then 1 else 0 end as ind
- from a left join b on a.year=b.year and a.qtr=b.qtr
- )
- group by ii, id, year, qtr, name;
- select distinct id, year, qtr, name, var1, var2 from c
- order by ii;
- quit;