HI @kk26 First off, It appears the values in your latest HAVE(Before) and WANT(After) are not consistent. For example, the ENDT value in before 26-Oct-18
203 cab 25-Oct-18 26-Oct-18 50 3
And in the after equivalent it is 27-Oct-18. Also new USSubjid in before
204 can 20-Nov-18 24-Nov-18 35 5
and the equivalent in after is 301. Though trivial, I failed to notice until I was wondering how SAS can get basic math wrong. So, please going forward verify the details 🙂
Secondly, I have a feeling, AFTER for
204 can 20-Nov-18 24-Nov-18 35 5
should ideally be the average dose 7 in place of 35 to be in consistent with the logic to apply for the previous i.e.
203 cab 23-Oct-18 24-Oct-18 80 2
203 cab 25-Oct-18 27-Oct-18 50 3
resulting in
203 cab 23OCT2018 27OCT2018 26 5
So assuming the above makes sense, the following solution would meet it
data have;
infile cards expandtabs truncover;
input USUBJID TRT $ (ASTDT ENDDT) (:date9.) DOSE DURATIONONDRUG;
format ASTDT ENDDT date9.;
cards;
102 cab 11-Apr-18 29-Apr-18 20 19
102 cab 30-Apr-18 30-Apr-18 10 1
102 cab 1-May-18 1-May-18 5 1
102 cab 2-May-18 13-May-18 15 12
102 cab 14-May-18 29-May-18 7.5 16
203 cab 21-Jun-18 4-Jul-18 60 14
203 cab 5-Jul-18 19-Jul-18 50 15
203 cab 20-Jul-18 25-Jul-18 40 6
203 cab 26-Jul-18 15-Aug-18 30 21
203 cab 16-Aug-18 12-Sep-18 25 28
203 cab 13-Sep-18 16-Sep-18 30 4
203 cab 17-Sep-18 18-Sep-18 25 2
203 cab 19-Sep-18 27-Sep-18 50 9
203 cab 28-Sep-18 15-Oct-18 80 18
203 cab 16-Oct-18 18-Oct-18 80 3
203 cab 19-Oct-18 22-Oct-18 80 4
203 cab 23-Oct-18 24-Oct-18 80 2
203 cab 25-Oct-18 27-Oct-18 50 3
204 can 20-Nov-18 24-Nov-18 35 5
;
data want;
do until(last.USUBJID);
set have;
by USUBJID;
if DURATIONONDRUG<7 then do;
if dt=. then dt=astdt;
s=sum(s,DURATIONONDRUG);
s1=sum(s1,dose);
if s>=7 or last.USUBJID then do;
astdt=dt;
ndays=intck('day',astdt,ENDDT)+1;
dose=s1/ndays;
DURATIONONDRUG=s;
output;
call missing(of s:,dt);
end;
continue;
end;
else if s and dt then do;
s=sum(s,DURATIONONDRUG);
s1=sum(s1,dose);
astdt=dt;
DURATIONONDRUG=s;
ndays=intck('day',astdt,ENDDT)+1;
dose=s1/ndays;
call missing(of s:,dt);
end;
output;
end;
drop s: dt ndays;
run;
Best Regards!
... View more