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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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