topic Re: Counter Reset After Condition in SAS Programming

Counter Reset After Condition

eabc0351 — Thu, 03 Mar 2022 03:35:22 GMT

Hello. I would like to create a counter variable based on a condition. I would like to begin the counter variable at the first.Date for the first.ID. Within each ID, the counter should count forward and then reset when the Date is more than 280 days from the where the counter started. Then do the same thing for the next ID- start at 1 in the first observation for that ID and count forward until the Date is >280 days from the original date and then restart. Could someone assist me with the code to execute this? Thank you

data want;
input ID Date YYMMDD10. counter;
format Date YYMMDD10.;
datalines;
1  2014-12-01   1
1  2015-12-31   2
1  2015-12-16   1
1  2015-12-18   2
2  2016-02-23   1
2  2018-03-05   1
2  2018-03-06   2
2  2018-03-25   3
3  2019-05-06   1
3  2019-08-08   2
3  2019-11-08   3
3  2019-11-25   4
3  2020-08-21   1
;
run;

Re: Counter Reset After Condition

ballardw — Thu, 03 Mar 2022 04:19:35 GMT

You either need to check your dates or go into more detail on what your "reset" rules actually are.

Consider the points indicate below with <=

data want;
input ID Date YYMMDD10. counter;
format Date YYMMDD10.;
datalines;
1  2014-12-01   1
1  2015-12-31   2
1  2015-12-16   1 <= within 280 days of 2014-12-01
1  2015-12-18   2 <= within 280 days of 2014-12-01 
2  2016-02-23   1
2  2018-03-05   1
2  2018-03-06   2
2  2018-03-25   3
3  2019-05-06   1
3  2019-08-08   2
3  2019-11-08   3
3  2019-11-25   4
3  2020-08-21   1
;
run;

This comes close except for the points in question above.

data have;
input ID Date :YYMMDD10. ;
format Date YYMMDD10.;
datalines;
1  2014-12-01
1  2015-12-31
1  2015-12-16
1  2015-12-18
2  2016-02-23
2  2018-03-05
2  2018-03-06
2  2018-03-25
3  2019-05-06
3  2019-08-08
3  2019-11-08
3  2019-11-25
3  2020-08-21
;

data want;
   set have;
   by id;
   retain counter basedate;
   if first.id then do;
      /* reset stuff*/
      basedate=date;
      counter=0;
   end;
   if intck('day',basedate,date) le 280 then counter+1;
   else do;
      counter=1;
      /* questionable as to when this resets*/
      basedate=date;
   end;
   drop basedate ;
run;

Re: Counter Reset After Condition

eabc0351 — Thu, 03 Mar 2022 13:44:45 GMT

Thanks @ballardw. This solution worked great. Yes, I made an error in the datalines for the last two observations for person 1. Thanks again.