DATA Step, Macro, Functions and more

Assign sequential id by id and date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

 

Any advice?


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;

View solution in original post


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 ]
Posted in reply to rajeshalwayswel

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:

 

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 105 views
  • 1 like
  • 3 in conversation