Hi @raja777pharma , it looks to me like you have two datasets, one of which is the exposure dosage (exdose) data, which plans to administer two dosage of treatment (14mg and 18mg, twice of each) to a subject on a certain date, and the date range is determined. In the other dataset, a disposition (second_ds) dataset, the date (i.e., the adt) for administer the treatment changed (or had adjustment) and two of the adt date are not in range, so for that two adt date, the dosage should be the one which was planned (in exdose dataset) to assign to the time range before that adt date. My code and output is as follows, and the result is the same as @quickbluefish 's (and a while later I found my steps in fact are the same with @Ksharp 's, except my steps is more basic and simple and @Ksharp combined all proc sql steps in one and use more advanced technique), please kindly let me know if I answered your question, thanks a lot!
data exdose;
input USUBJID $ EXDOSE
stdt :date9. endt :date9.;
format stdt endt date9.;
datalines;
1361007 14 16MAY2025 18MAY2025
1361007 14 20MAY2025 30MAY2025
1361007 18 31MAY2025 14JUN2025
1361007 18 16JUN2025 18JUN2025
;
run;
proc print data=exdose;run;
data second_ds;
input USUBJID $ ADT date9.;
format adt date9.;
datalines;
1361007 17MAY2025
1361007 19MAY2025
1361007 30MAY2025
1361007 15JUN2025
;
run;
proc print data=second_ds;run;
/*if adt date in the second_ds dataset
is not in range, according to request,
find the earliest date and last date
of the exposure dosage, and produce
a dataset that has two date ranges*/
proc sql;
create table t_range as
select usubjid,
exdose,
min(stdt) as stdtmin
format=date9.,
max(endt) as endtmax
format=date9.
from exdose
group by usubjid,
exdose;
select * from t_range;
quit;
proc sql;
create table t_range2 as
select ed.*,
tr.stdtmin,
tr.endtmax
from exdose as ed left join
t_range as tr
on ed.usubjid=tr.usubjid and
ed.exdose=tr.exdose;
select * from t_range2;
quit;
/*determine which date range the adt date
belongs to, and assign the correspondence
dosage to the adt date*/
proc sql;
create table final1 as
select ds2.*,
tr2.exdose
from second_ds as ds2 left join
t_range2 as tr2
on ds2.usubjid=tr2.usubjid
where tr2.stdt<=ds2.adt<=tr2.endt;
select * from final1;
quit;
proc sql;
create table final2 as
select ds2.*,
tr2.exdose
from second_ds as ds2 left join
t_range2 as tr2
on ds2.usubjid=tr2.usubjid
where tr2.stdtmin<=ds2.adt<=tr2.endtmax
except
select ds2.*,
tr2.exdose
from second_ds as ds2 left join
t_range2 as tr2
on ds2.usubjid=tr2.usubjid
where tr2.stdt<=ds2.adt<=tr2.endt;
select * from final2;
quit;
proc sql;
create table ds2_exdose as
select * from final1
union
select * from final2
order by usubjid,adt;
select * from ds2_exdose;
quit;
... View more
