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

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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
  • 1145 views
  • 0 likes
  • 2 in conversation