## Assign sequential id by id and date

Solved
Occasional Contributor
Posts: 19

# Assign sequential id by id and date

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

Accepted Solutions
Solution
2 weeks ago
Frequent Contributor
Posts: 86

## Re: Assign sequential id by id and date

``````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;``````

All Replies
Solution
2 weeks ago
Frequent Contributor
Posts: 86

## Re: Assign sequential id by id and date

``````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;``````
Occasional Contributor
Posts: 19

## Re: Assign sequential id by id and date

[ Edited ]

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!

Super User
Posts: 13,293

## Re: Assign sequential id by id and date

@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:

```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;```
☑ This topic is solved.