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;
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;
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;
Thanks @ballardw. This solution worked great. Yes, I made an error in the datalines for the last two observations for person 1. Thanks again.
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.
Ready to level-up your skills? Choose your own adventure.