[code:22uti9ru]data new;
set jingju; by id;
length start stop 8 drugs $100 lagdrug $4;/*drugs=drug list*/
retain drugs;
lagdrug = lag(drug); lagdate = lag(date); lagtype = lag(type);
if first.id then drugs = '';
start = lagdate; stop = date;
if lagtype = 'L' then drugs = cats(drugs, '/', lagdrug);
else if lagtype = 'R' then drugs = tranwrd(drugs, lagdrug, '');
/*remove unncessary '/'s*/
drugs = compress(drugs);
if first(drugs) = '/' then drugs = substrn(drugs, 2);
if first(left(reverse(drugs))) = '/' then drugs = substrn(drugs, 1, length(drugs)-1);
drugs = tranwrd(drugs, '//', '/');
if ^first.id;
keep id start stop drugs;
run;[/code:22uti9ru]
运行结果如下：
Obs id start stop drugs
01 1 0000 0031 ZIDO
02 1 0031 0214 ZIDO/ZALC
03 1 0214 1339 ZIDO
04 1 1339 1346 ZIDO/DIDA
05 1 1346 1400 ZIDO
06 1 1400 1431 ZIDO/LAMI
07 1 1431 1431 ZIDO
08 1 1431 1514 ZIDO/LAMI
09 1 1514 1826 LAMI
10 1 1826 1857
11 1 1857 2102 LAMI
12 1 2102 2410 LAMI/SAQU
13 1 2410 2410 LAMI
14 1 2410 3113 LAMI/INDI
15 1 3113 3113 INDI
16 1 3113 3113
17 1 3113 3113 NELF
18 1 3113 3113 NELF/ABAC
19 1 3113 3193 NELF/ABAC/EFAV
20 1 3193 3193 ABAC/EFAV
21 1 3193 3285 ABAC/EFAV/NELF
22 1 3285 3285 ABAC/EFAV
23 1 3285 3285 ABAC/EFAV/STAV
24 1 3285 3621 ABAC/EFAV/STAV/LAMI
25 1 3621 3705 ABAC/EFAV/LAMI
26 1 3705 5721 ABAC/EFAV/LAMI/KALE
27 1 5721 5721 EFAV/LAMI/KALE
28 1 5721 7201 EFAV/KALE
29 1 7201 7201 KALE
再次感谢。除掉你不凡的编程技巧，把start和stop排成一行，是关键。作者: shiyiming 时间: 2010-2-25 10:55 标题: Re: 请教诸位： I wish this simplified approach helps (or at least addresses the problem at all). The output may be refined to meet the requirement more specific. E.g., some rows in the output can be further combined with duplicated items removed, etc., if desired. <!-- s:-) --><img src="{SMILIES_PATH}/icon_smile.gif" alt=":-)" title="Smile" /><!-- s:-) -->
[code:2h9ay8o8]proc sql;
create table myData as
select *,
monotonic() as rowID
from myData
order by id,
start,
stop,
drug;
quit;
proc sql;
create table semi_final as
select a.id,
max(a.start, b.start, c.start) as start,
min(a.stop, b.stop, c.stop) as stop,
trim(a.drug) || '/' || trim(b.drug) || '/' || trim(c.drug) as drugs
from myData as a,
myData as b,
myData as c
where a.rowID < b.rowID < c.rowID and
a.id = b.id = c.id and
a.drug ^= b.drug and b.drug ^= c.drug and a.drug ^= c.drug and
max(a.start, b.start, c.start) <= min(a.stop, b.stop, c.stop)
order by a.id,
calculated start,
calculated stop,
calculated drugs;
quit;
data final(drop = drugs i
rename = (final_drugs = drugs)
);
set semi_final;
by id
start
stop
drugs;
retain final_drugs;
length final_drugs $200;
if first.stop then final_drugs = drugs;
do i = 1 to 3;
if index(final_drugs, strip(scan(drugs, i, '/'))) = 0 then final_drugs = strip(final_drugs) || '/' || strip(scan(drugs, i, '/'));
end;