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;
