BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eabc0351
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

View solution in original post

2 REPLIES 2
ballardw
Super User

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;
eabc0351
Quartz | Level 8

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1225 views
  • 0 likes
  • 2 in conversation