Hello all!
I have a question about how to assign a sequential number to each subject based on another variable that is a date.
This would be the start dataset:
id date
1 1/10/07
1 3/23/07
1 5/23/08
2 2/10/07
2 2/01/08
3 8/01/12
3 1/23/13
3 2/10/14
3 7/10/15
This is what I want as the end result:
id date seq
1 1/10/07 1
1 3/23/07 2
1 5/23/08 3
2 2/10/07 1
2 2/01/08 2
3 8/01/12 1
3 1/23/13 2
3 2/10/14 3
3 7/10/15 4
Any advice?
data a;
infile datalines missover;
input id date $10. ;
datalines;
1 1/10/07
1 3/23/07
1 5/23/08
2 2/10/07
2 2/01/08
3 8/01/12
3 1/23/13
3 2/10/14
3 7/10/15
run;
proc sort data=a out=a1;by id;run;
data a2;
set a1;
by id;
if first.id then seq=1 ;
else seq+1;
run;
data a;
infile datalines missover;
input id date $10. ;
datalines;
1 1/10/07
1 3/23/07
1 5/23/08
2 2/10/07
2 2/01/08
3 8/01/12
3 1/23/13
3 2/10/14
3 7/10/15
run;
proc sort data=a out=a1;by id;run;
data a2;
set a1;
by id;
if first.id then seq=1 ;
else seq+1;
run;
Thank you so much! It worked!
The only thing that I changed is that when I did proc sort, I did this:
proc sort data=dataset;
by id date;
run;
Not sure if it would have still worked with only sorting by id...but I sorted by both id and date since I wanted the Seq to be in the order of the dates.
Thanks again!
@SarahW13 wrote:
Thank you so much! It worked!
The only thing that I changed is that when I did proc sort, I did this:
proc sort data=dataset;
by id date;
run;
Not sure if it would have still worked with only sorting by id...but I sorted by both id and date since I wanted the Seq to be in the order of the dates.
Thanks again!
You inclusion of date is correct.
If you change the order of the data as read, switched the 2nd and 3rd rows and sort by Id the values are not in the desired order:
data a; infile datalines missover; input id date $10. ; datalines; 1 1/10/07 1 5/23/08 1 3/23/07 2 2/10/07 2 2/01/08 3 8/01/12 3 1/23/13 3 2/10/14 3 7/10/15 run; proc sort data=a; by id; run;
But I strongly suggest using a DATE value instead of character as otherwise your data will not sort correctly with the date:
Please see:
data a; infile datalines missover; input id date $10. ; datalines; 1 1/10/07 1 5/23/08 1 3/23/07 1 11/10/04 2 2/10/07 2 2/01/08 3 8/01/12 3 1/23/13 3 2/10/14 3 7/10/15 run; proc sort data=a; by id date; run;
Note that 11/10/04 comes after 1/10/07. Character sort goes character by character left to right.
So something more like this is likely actually what you want:
data a; infile datalines missover; input id date mmddyy8. ; format date mmddyy8.; datalines; 1 1/10/07 1 5/23/08 1 3/23/07 1 11/10/04 2 2/10/07 2 2/01/08 3 8/01/12 3 1/23/13 3 2/10/14 3 7/10/15 run; proc sort data=a; by id date; 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.