topic Re: Assign sequential id by id and date in SAS Programming

Assign sequential id by id and date

SarahW13 — Wed, 06 Jun 2018 06:16:19 GMT

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?

Re: Assign sequential id by id and date

rajeshalwayswel — Wed, 06 Jun 2018 06:53:19 GMT

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;

Re: Assign sequential id by id and date

SarahW13 — Wed, 06 Jun 2018 06:59:30 GMT

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!

Re: Assign sequential id by id and date

ballardw — Wed, 06 Jun 2018 22:17:44 GMT

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