data have; length epoch $10. ; input usubjid sstdtc sendtc epoch $ count; informat sstdtc date9. sendtc date9.; format sstdtc sendtc yymmdd10.; datalines; 100 01JAN2010 15JAN2010 Screening 1 100 15JAN2010 15JUL2010 Treatment 2 100 16JUL2010 20DEC2010 Follow-up 3 ; run; proc sort data = have out = have_sort; by usubjid count; run; data have1; input usubjid adtc ; informat adtc date9.; format adtc yymmdd10.; datalines; 100 15JAN2010 100 14AUG2010 ; run; proc sort data= have1 out = have1_sort; by usubjid; run; Proc Sql; Create Table want_sql as select a.usubjid, adtc , epoch, count, sstdtc, sendtc From have1 as a left join have as b on a.usubjid= b.usubjid where sstdtc <= adtc <=sendtc order by usubjid ,adtc,count; Quit; proc print data = want_sql; title'proc sql result'; run; /****proc transpose method******/ proc sql noprint; create table _have as select usubjid, epoch , sstdtc as start, sendtc as end ,count from have order by usubjid, epoch,count; quit; proc sort data = _have out= have_s; by usubjid count; run; proc transpose data=have_s out=s_t; by usubjid count; var epoch start end; run; proc transpose data=s_t out=set_f(drop=_name_) delimiter=_; by usubjid; id _name_ count; var col1; run; data se_f; merge s_t (in=ina) set_f (in=inb); by usubjid; drop col1; run; proc sort data = se_f ; by usubjid ; run; data want; merge have1 (in=ina) se_f(in=inf); by usubjid; if ina; array sstdtc(3) start_1-start_3; array sendtc(3) end_1-end_3; array ep(3) epoch_1-epoch_3; do i=1 to 3; sestd=strip(sstdtc(i)); seend=strip(sendtc(i)); put adtc; aestd=put(adtc,yymmdd10.); format aestd yymmdd10.; put sestd; put aestd; put seend; put ep(i); if sestd <= aestd <= seend then do; epoch = ep(i); leave; end; end; drop _name_ epoch_1 epoch_2 epoch_3 start_1 start_2 start_3 end_1 end_2 end_3 aestd i; run; data want_transpose; set want; rename sestd=sstdtc seend = sendtc; run; proc sort data =want_transpose nodupkey ; by usubjid count; run; proc print data=want_transpose; title 'proc transpose result'; run; proc compare base = want_sql compare= want_transpose; run;
... View more