I asked why you have 355 (not 335). Your answer now raises another question. You chose to ignore Dec 31,2016 because it's the 366th day of the year, thereby getting 334 total days for 1A. Does that mean someone with coverage from Jan 1-Dec 30 has 1 day less than Jan 2-Dec 31?
Anyhow, assuming that's what you mean, then this should work:
data want (drop=n j d first_n cutdate mindate overlap);
/* Read all recs for a patid, to determine which rec has earliest SERV_DATE1 */
do N=1 by 1 until (last.patid);
set testing;
by patid;
if serv_date1<mindate or N=1 then first_N=N;
mindate=min(mindate,serv_date1);
end;
cutdate="01jan2016"d + 365; /* Earliest day to NOT include */
array sd {*} serv_date: ;
array ds {*} days_supply: ;
/* Reread all N recs for a patid, and calc coverage days*/
do J=1 to N;
set testing;
days=0;
length class $13;
if N=1 then class='MONO';
else if J=first_n then class='First Therapy';
else class='Add On';
do d=1 to dim(sd) while (ds{d}^=.);
if d>1 then overlap = max(0,sd{d-1}+ds{d-1}+1-sd{d});
else overlap=0;
days=days+min(cutdate-sd{d},ds{d})-overlap;
end;
output;
end;
run;
If this is a solution to your original task, then it shows the value and time savings of a clearly stated problem accompanied by an input example and a correct output example.
This solution uses proc sort, which I usually like to avoid because the result file does not have the same record order as the source data (often unwanted) and sorting large files can be expensive. However:
proc sort data=testing out=need;
by patid serv_date1;
run;
data want (drop=d cutdate overlap end_date);
set need;
by patid;
cutdate="01jan2016"d + 365; /* Earliest day to NOT include */
array sd {*} serv_date: ;
array ds {*} days_supply: ;
days=0;
do d=1 to dim(sd) while (ds{d}^=.);
if d>1 then overlap = max(0,sd{d-1}+ds{d-1}+1-sd{d});
else overlap=0;
days = days + min(cutdate-sd{d},ds{d})-overlap;
end;
end_date = serv_date1 + days -1;
length class $13;
class =ifc(serv_date1>lag(end_date),'SWITCH','ADD ON');
if first.patid=1 and last.patid=1 then class='MONO';
else if first.patid then class='First Therapy';
run;
Hi Mkeintz,
I tried to use your code but the days covered were not coming out to what I expected.
I developed this code below and it give me the correct days covered. I also used your code to determine the class. That was great! I liked your code because it was short and simple but unfortunately it wasn't giving me the correct days covered.
data WORK.TESTING;
infile datalines dsd truncover;
input PATID:32. Therapy:$1. serv_date1:DATE9. serv_date2:DATE9. serv_date3:DATE9. serv_date4:DATE9. serv_date5:DATE9. days_supply1:32. days_supply2:32. days_supply3:32. days_supply4:32. days_supply5:32.;
datalines4;
1,A,11JAN2016,13APR2016,18JUL2016,28OCT2016,,90,90,90,90,
1,B,18JUN2016,22SEP2016,13DEC2016,28DEC2016,,90,90,30,30,
2,A,11JAN2016,18APR2016,14JUL2016,13OCT2016,,90,90,90,90,
2,B,11JAN2016,16APR2016,18JUL2016,13OCT2016,22NOV2016,90,90,90,90,90
3,A,02MAR2016,29AUG2016,05NOV2016,,,90,90,90,,
3,B,05FEB2016,28JUN2016,06SEP2016,02DEC2016,,90,90,90,90,
3,C,26MAR2016,27JUN2016,06SEP2016,29NOV2016,,90,90,90,90,
4,A,01JAN2016,01APR2016,13APR2016,31DEC2016,,90,90,90,30,
5,A,01JAN2016,01FEB2016,01MAR2016,01APR2016,,30,30,30,30,
5,B,01MAY2016,01JUN2016,01JUL2016,01AUG2016,,30,30,30,30,
;;;;
proc sort data=testing out=need;
by patid serv_date1;
run;
data want;
set need;
format start_dt end_dt date9.;
start_dt=serv_date1;
end_dt=serv_date1+365;
run;
data want1 (keep=patid Therapy serv_date1 - serv_date5 days_supply1-days_supply5 dayscovered);
set want;
format adj_serv_date1- adj_serv_date5 date9.;
array adjfilldat(*)adj_serv_date1 - adj_serv_date5;
array daydummy(365) day1-day365;
array filldates(*) serv_date1 - serv_date5;
array days_supply(*) days_supply1-days_supply5;
/*adjust fill dates*/
do p = 1 to 5;
adjfilldat(p) = '01Jan2016'd;
adjfilldat(p)= filldates(p);
end;
drop p;
do u=2 to 5 while (adjfilldat(u) ne .);
if adjfilldat(u)<adjfilldat(u-1)+days_supply(u-1)
then adjfilldat(u)=adjfilldat(u-1)+days_supply(u-1);
end;
drop u;
start_dt_au = '01Jan2016'd;
dumm_start = start_dt-start_dt_au;
do ii=1 to 365; daydummy(ii)=0;end;
do ii=dumm_start to 365;
do i = 1 to dim(adjfilldat) while (adjfilldat(i) ne .);
if adjfilldat(i)<= start_dt_au + ii -1 <= adjfilldat(i)+days_supply(i)-1
then daydummy(ii)=1;
end;
end;
drop i ii;
dayscovered=sum(of day1 - day365);label dayscovered='Total Days Covered';
p_dayscovered=dayscovered/365;label p_dayscovered='Proportion of Days Covered';
run;
proc sort data=want1 out=want2;
by patid serv_date1;
run;
data Final;
set want2;
by patid;
end_date = serv_date1 + dayscovered -1;
length class $13;
class =ifc(serv_date1>lag(end_date),'SWITCH','ADD ON');
if first.patid=1 and last.patid=1 then class='MONO';
else if first.patid then class='First Therapy';
run;
Output
PATID | Therapy | serv_date1 | serv_date2 | serv_date3 | serv_date4 | serv_date5 | days_supply1 | days_supply2 | days_supply3 | days_supply4 | days_supply5 | dayscovered | class |
1 | A | 1/11/2016 | 4/13/2016 | 7/18/2016 | 10/28/2016 | 90 | 90 | 90 | 90 | 334 | First Therapy | ||
1 | B | 6/18/2016 | 9/22/2016 | 12/13/2016 | 12/28/2016 | 90 | 90 | 30 | 30 | 190 | ADD ON | ||
2 | A | 1/11/2016 | 4/18/2016 | 7/14/2016 | 10/13/2016 | 90 | 90 | 90 | 90 | 347 | First Therapy | ||
2 | B | 1/11/2016 | 4/16/2016 | 7/18/2016 | 10/13/2016 | 11/22/2016 | 90 | 90 | 90 | 90 | 90 | 346 | ADD ON |
3 | B | 2/5/2016 | 6/28/2016 | 9/6/2016 | 12/2/2016 | 90 | 90 | 90 | 90 | 276 | First Therapy | ||
3 | A | 3/2/2016 | 8/29/2016 | 11/5/2016 | 90 | 90 | 90 | 214 | ADD ON | ||||
3 | C | 3/26/2016 | 6/27/2016 | 9/6/2016 | 11/29/2016 | 90 | 90 | 90 | 90 | 277 | ADD ON | ||
4 | A | 1/1/2016 | 4/1/2016 | 4/13/2016 | 12/31/2016 | 90 | 90 | 90 | 30 | 270 | MONO | ||
5 | A | 1/1/2016 | 2/1/2016 | 3/1/2016 | 4/1/2016 | 30 | 30 | 30 | 30 | 120 | First Therapy | ||
5 | B | 5/1/2016 | 6/1/2016 | 7/1/2016 | 8/1/2016 | 30 | 30 | 30 | 30 | 120 | SWITCH |
Thank you so much for your help!
Does that mean someone with coverage from Jan 1-Dec 30 has 1 day less than Jan 2-Dec 31? No they should not. If th patient filled on 12/31/2016, it should be included.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.