Hi Everyone,
I have some data, below is a test sample of what I have .
data have;
input ID $ date:mmddyy10. value;
format date mmddyy10.;
datalines;
1 12/01/2020 5
1 12/02/2020 6
1 12/03/2020 7
2 12/01/2020 10
2 12/02/2020 8
2 12/03/2020 9
3 12/01/2020 2
3 12/02/2020 2
3 12/03/2020 2
4 12/01/2020 5
4 12/02/2020 8
4 12/03/2020 6
5 12/01/2020 2
5 12/02/2020 5
5 12/03/2020 4
;
run;
Essentially what I want to do is per ID number, I want to create a new variable called day and have it number from the first date to the last date as day 1, 2, 3, etc. Below is an example of what I would want to do.
data have;
input ID $ date:mmddyy10. value day;
format date mmddyy10.;
datalines;
1 12/01/2020 5 1
1 12/02/2020 6 2
1 12/03/2020 7 3
2 12/01/2020 10 1
2 12/02/2020 8 2
2 12/03/2020 9 3
3 12/01/2020 2 1
3 12/02/2020 2 2
3 12/03/2020 2 3
4 12/01/2020 5 1
4 12/02/2020 8 2
4 12/03/2020 6 3
5 12/01/2020 2 1
5 12/02/2020 5 2
5 12/03/2020 4 3
;
run;
I tried to originally create a do loop with first.date and last.date but I wasn't sure how to incorporate the ID number as well, and when I tried to I kept getting errors 😅. Any ideas on how to create this? Thank you!
Use proc rank:
proc rank data=have out=want;
by id;
var date;
ranks day;
run;
Use proc rank:
proc rank data=have out=want;
by id;
var date;
ranks day;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.