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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.