I have provided a deidentified screenshot with dummy values to demonstrate my data. I can't figure out how to identify 3 dates that fall within a 14 day period of time for each patient for a specific medication. Please note that some patients have more than 100 occurrences for a given medication (i.e. >100 dates).
For example, starting with patient 34444862 obs=3-7 (table long) and/or obs=6 (table wide). I would like to capture 27APR2018 (obs=7) as the only time Bupropion occurred three times within a 14 day period.
For patient 34443625 obs=12-26 (table long) and/or obs=11 (table wide). I would like to capture 03APR2018 as the first time 3 Sertralines occurred within 14 days. I also need to capture 06APR2018, 10APR2018, 13APR2018, and 16APR2018 as the next dates.
I have tried using arrays (table wide) and variations of the dif() function (table long) and I am banging my head against a wall with a looming deadline. Any help you can provide is greatly appreciated! Thank you in advance.
I am using SAS 9.4 and I have attached the dummy data.
Hi,
Here's updated code, that I believe will solve the problem. I'm making use of BY group processing (Last./First.) referenced in the earlier post and the LAG function
In the code below I added a check variable which I manually set to 1 for records that meet your required output, as I understand it.
Test the code and please confirm if it solves your question
data long ;
format date date. ;
infile cards ;
input
patientId
treatment : $32.
date : date.
check $ ;
cards;
00000001 TEST 1-Apr-18 0
00000001 TEST 5-Apr-18 0
00000001 TEST 15-Apr-18 0
00000001 TEST 16-Apr-18 1
00000001 TEST 30-Apr-18 0
00000001 TEST 05-May-18 0
00000001 TEST 15-May-18 0
00000001 TEST 18-May-18 1
34444486 FLUOXETINE 4-Apr-18 0
34444486 FLUOXETINE 14-Apr-18 0
34444862 BUPROPION 1-Apr-18 0
34444862 BUPROPION 4-Apr-18 0
34444862 BUPROPION 20-Apr-18 0
34444862 BUPROPION 27-Apr-18 0
34444862 BUPROPION 27-Apr-18 1
34443348 OLANZAPINE 9-Apr-18 0
34443348 OLANZAPINE 10-Apr-18 0
34443348 OLANZAPINE 29-Apr-18 0
34443399 TRAZODONE 12-Apr-18 0
34443625 SERTRALINE 1-Apr-18 0
34443625 SERTRALINE 2-Apr-18 0
34443625 SERTRALINE 3-Apr-18 1
34443625 SERTRALINE 4-Apr-18 0
34443625 SERTRALINE 5-Apr-18 0
34443625 SERTRALINE 6-Apr-18 1
34443625 SERTRALINE 7-Apr-18 0
34443625 SERTRALINE 8-Apr-18 0
34443625 SERTRALINE 10-Apr-18 1
34443625 SERTRALINE 11-Apr-18 0
34443625 SERTRALINE 12-Apr-18 0
34443625 SERTRALINE 13-Apr-18 1
34443625 SERTRALINE 14-Apr-18 0
34443625 SERTRALINE 15-Apr-18 0
34443625 SERTRALINE 16-Apr-18 1
34443874 FLUOXETINE 7-Apr-18 0
34443874 OLANZAPINE 7-Apr-18 0
34443232 BUPROPION 6-Apr-18 0
34443232 BUPROPION 8-Apr-18 0
34443232 BUPROPION 10-Apr-18 1
34443232 BUPROPION 11-Apr-18 0
34443232 BUPROPION 12-Apr-18 0
34443232 BUPROPION 14-Apr-18 1
34443232 BUPROPION 15-Apr-18 0
34443232 BUPROPION 15-Apr-18 0
34443232 BUPROPION 16-Apr-18 1
34443232 BUPROPION 17-Apr-18 0
34443232 BUPROPION 21-Apr-18 0
34443232 BUPROPION 22-Apr-18 1
34443232 BUPROPION 29-Apr-18 0
34443232 BUPROPION 29-Apr-18 0
;
run ;
proc sort ;
by patientId treatment date ;
run ;
data alerts ;
retain
treatmentCount
firstDate ;
set long ;
*put "S : " treatmentCount= patientId= treatment= firstDate= date. date= ;
patientIdL2=lag2(patientID) ;
treatmentL2=lag2(treatment) ;
dateL2 =lag2(date) ;
patientIdL1=lag1(patientID) ;
treatmentL1=lag1(treatment) ;
dateL1 =lag1(date) ;
by patientId treatment date ;
/* first.treatment is set to 1 when it's the first occurance of a new treatment or first occurance of a new patientID */
if first.treatment or treatmentCount=3 then do ;
treatmentCount=1 ;
firstDate=date ;
end ;
/* If it is not the first occurance of a treatment or patientID */
else do ;
/* Is the current treatment date within 14 days of the first treatment date */
if date-firstDate<14 then do ;
*put "<14" date 8. firstdate 8. ;
/* increase treatmentCounter */
treatmentCount=treatmentCount+1 ;
/* If it is the 3rd treatment within 14 days */
if treatmentCount=3 then do ;
*put ">>>" treatmentCount= patientId= treatment= firstDate= date. date= ;
output ;
end ;
end ;
if date-firstDate>=14 then do ;
*put ">=14" date 8. firstdate 8. ;
*put dateL1= date. dateL2= date. ;
if patientIdL2=patientId and
treatmentL2=treatment and
date-dateL2<14 then do ;
treatmentCount=2 ;
firstDate=dateL2 ;
end ;
else if patientIdL1=patientId and
treatmentL1=treatment and
date-dateL1<14 then do ;
treatmentCount=2 ;
firstDate=dateL1 ;
end ;
end ;
end ;
*put "E : " treatmentCount= patientId= treatment= firstDate= date. date= ;
run ;
Hi,
This can be achieved using first. & last. processing in the datastep
I've provided an example using a subset of your long file below
Andy
data long ; format date date. ; infile cards ; input patientId treatment : $32. date : date. ; cards; 34444486 FLUOXETINE 4-Apr-18 34444486 FLUOXETINE 14-Apr-18 34444862 BUPROPION 1-Apr-18 34444862 BUPROPION 4-Apr-18 34444862 BUPROPION 20-Apr-18 34444862 BUPROPION 27-Apr-18 34444862 BUPROPION 27-Apr-18 34443348 OLANZAPINE 9-Apr-18 34443348 OLANZAPINE 10-Apr-18 34443348 OLANZAPINE 29-Apr-18 34443399 TRAZODONE 12-Apr-18 34443625 SERTRALINE 1-Apr-18 34443625 SERTRALINE 2-Apr-18 34443625 SERTRALINE 3-Apr-18 34443625 SERTRALINE 4-Apr-18 34443625 SERTRALINE 5-Apr-18 34443625 SERTRALINE 6-Apr-18 34443625 SERTRALINE 7-Apr-18 34443625 SERTRALINE 8-Apr-18 34443625 SERTRALINE 10-Apr-18 34443625 SERTRALINE 11-Apr-18 34443625 SERTRALINE 12-Apr-18 34443625 SERTRALINE 13-Apr-18 34443625 SERTRALINE 14-Apr-18 34443625 SERTRALINE 15-Apr-18 34443625 SERTRALINE 16-Apr-18 34443874 FLUOXETINE 7-Apr-18 34443874 OLANZAPINE 7-Apr-18 34443232 BUPROPION 6-Apr-18 34443232 BUPROPION 8-Apr-18 34443232 BUPROPION 10-Apr-18 34443232 BUPROPION 11-Apr-18 34443232 BUPROPION 12-Apr-18 34443232 BUPROPION 14-Apr-18 34443232 BUPROPION 15-Apr-18 34443232 BUPROPION 15-Apr-18 34443232 BUPROPION 16-Apr-18 34443232 BUPROPION 17-Apr-18 34443232 BUPROPION 21-Apr-18 34443232 BUPROPION 22-Apr-18 34443232 BUPROPION 29-Apr-18 34443232 BUPROPION 29-Apr-18 ; run ; proc sort ; by patientId treatment date ; run ; data alerts ; retain treatmentCount firstDate ; set long; by patientId treatment date ; /* first.treatment is set to 1 when it's the first occurance of a new treatment or first occurance of a new patientID */ if first.treatment or treatmentCount=3 then do ; treatmentCount=1 ; firstDate=date ; put treatmentCount= patientId= treatment= firstDate= date. date= ; end ; /* If it is not the first occurance of a treatment or patientID */ else do ; put treatmentCount= patientId= treatment= firstDate= date. date= ; /* Is the current treatment date within 14 days of the first treatment date */ if date-firstDate<14 then do ; /* increase treatmentCounter */ treatmentCount=treatmentCount+1 ; /* If is the 3rd treatment within 14 days */ if treatmentCount=3 then do ; put ">>>" treatmentCount= patientId= treatment= firstDate= date. date= ; output ; end ; end ; else treatmentCount=1 ; firstDate=date ; end ; run ;
Hi,
Here's updated code, that I believe will solve the problem. I'm making use of BY group processing (Last./First.) referenced in the earlier post and the LAG function
In the code below I added a check variable which I manually set to 1 for records that meet your required output, as I understand it.
Test the code and please confirm if it solves your question
data long ;
format date date. ;
infile cards ;
input
patientId
treatment : $32.
date : date.
check $ ;
cards;
00000001 TEST 1-Apr-18 0
00000001 TEST 5-Apr-18 0
00000001 TEST 15-Apr-18 0
00000001 TEST 16-Apr-18 1
00000001 TEST 30-Apr-18 0
00000001 TEST 05-May-18 0
00000001 TEST 15-May-18 0
00000001 TEST 18-May-18 1
34444486 FLUOXETINE 4-Apr-18 0
34444486 FLUOXETINE 14-Apr-18 0
34444862 BUPROPION 1-Apr-18 0
34444862 BUPROPION 4-Apr-18 0
34444862 BUPROPION 20-Apr-18 0
34444862 BUPROPION 27-Apr-18 0
34444862 BUPROPION 27-Apr-18 1
34443348 OLANZAPINE 9-Apr-18 0
34443348 OLANZAPINE 10-Apr-18 0
34443348 OLANZAPINE 29-Apr-18 0
34443399 TRAZODONE 12-Apr-18 0
34443625 SERTRALINE 1-Apr-18 0
34443625 SERTRALINE 2-Apr-18 0
34443625 SERTRALINE 3-Apr-18 1
34443625 SERTRALINE 4-Apr-18 0
34443625 SERTRALINE 5-Apr-18 0
34443625 SERTRALINE 6-Apr-18 1
34443625 SERTRALINE 7-Apr-18 0
34443625 SERTRALINE 8-Apr-18 0
34443625 SERTRALINE 10-Apr-18 1
34443625 SERTRALINE 11-Apr-18 0
34443625 SERTRALINE 12-Apr-18 0
34443625 SERTRALINE 13-Apr-18 1
34443625 SERTRALINE 14-Apr-18 0
34443625 SERTRALINE 15-Apr-18 0
34443625 SERTRALINE 16-Apr-18 1
34443874 FLUOXETINE 7-Apr-18 0
34443874 OLANZAPINE 7-Apr-18 0
34443232 BUPROPION 6-Apr-18 0
34443232 BUPROPION 8-Apr-18 0
34443232 BUPROPION 10-Apr-18 1
34443232 BUPROPION 11-Apr-18 0
34443232 BUPROPION 12-Apr-18 0
34443232 BUPROPION 14-Apr-18 1
34443232 BUPROPION 15-Apr-18 0
34443232 BUPROPION 15-Apr-18 0
34443232 BUPROPION 16-Apr-18 1
34443232 BUPROPION 17-Apr-18 0
34443232 BUPROPION 21-Apr-18 0
34443232 BUPROPION 22-Apr-18 1
34443232 BUPROPION 29-Apr-18 0
34443232 BUPROPION 29-Apr-18 0
;
run ;
proc sort ;
by patientId treatment date ;
run ;
data alerts ;
retain
treatmentCount
firstDate ;
set long ;
*put "S : " treatmentCount= patientId= treatment= firstDate= date. date= ;
patientIdL2=lag2(patientID) ;
treatmentL2=lag2(treatment) ;
dateL2 =lag2(date) ;
patientIdL1=lag1(patientID) ;
treatmentL1=lag1(treatment) ;
dateL1 =lag1(date) ;
by patientId treatment date ;
/* first.treatment is set to 1 when it's the first occurance of a new treatment or first occurance of a new patientID */
if first.treatment or treatmentCount=3 then do ;
treatmentCount=1 ;
firstDate=date ;
end ;
/* If it is not the first occurance of a treatment or patientID */
else do ;
/* Is the current treatment date within 14 days of the first treatment date */
if date-firstDate<14 then do ;
*put "<14" date 8. firstdate 8. ;
/* increase treatmentCounter */
treatmentCount=treatmentCount+1 ;
/* If it is the 3rd treatment within 14 days */
if treatmentCount=3 then do ;
*put ">>>" treatmentCount= patientId= treatment= firstDate= date. date= ;
output ;
end ;
end ;
if date-firstDate>=14 then do ;
*put ">=14" date 8. firstdate 8. ;
*put dateL1= date. dateL2= date. ;
if patientIdL2=patientId and
treatmentL2=treatment and
date-dateL2<14 then do ;
treatmentCount=2 ;
firstDate=dateL2 ;
end ;
else if patientIdL1=patientId and
treatmentL1=treatment and
date-dateL1<14 then do ;
treatmentCount=2 ;
firstDate=dateL1 ;
end ;
end ;
end ;
*put "E : " treatmentCount= patientId= treatment= firstDate= date. date= ;
run ;
Thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.