Hi all,
I have a question in regards to sequencing in SAS. I have a large dataset of dates of birth that I would like to assign unique identifiers to, since they did not come with one. If there are two cells in a row that have the same date of birth, they are the same person and should have the same unique ID. Below is an example of what I would like my dataset to look like:
DOB | ID |
9/9/2000 | 1 |
9/9/2000 | 1 |
8/11/1993 | 2 |
7/7/1987 | 3 |
7/7/1987 | 3 |
7/7/1987 | 3 |
2/28/2001 | 4 |
2/28/2001 | 4 |
10/18/1967 | 5 |
I'm just not sure how to sequence correctly in SAS so if a date of birth is equal directly after another date of birth, that they are assigned the same unique ID. And if the date of birth is different, then a new unique ID is assigned. Thank you for help in advance.
The programming isn't difficult ... it might be as easy as:
data want;
set have;
by DOB notsorted;
if first.DOB then ID + 1;
run;
Defining the problem is another story. For example, do the DOB values have to be consecutive in the data for them to represent the same person? (The sample solution assumes that they have to be consecutive.) Do you have an additional variable you could use to make sure the matching DOB values are actually the same person? It would be entirely possible for two consecutive people to have the same DOB. Depending on what you mean by a "large" data set, it might be more or less likely that this will occur.
The programming isn't difficult ... it might be as easy as:
data want;
set have;
by DOB notsorted;
if first.DOB then ID + 1;
run;
Defining the problem is another story. For example, do the DOB values have to be consecutive in the data for them to represent the same person? (The sample solution assumes that they have to be consecutive.) Do you have an additional variable you could use to make sure the matching DOB values are actually the same person? It would be entirely possible for two consecutive people to have the same DOB. Depending on what you mean by a "large" data set, it might be more or less likely that this will occur.
Could also use proc rank; by dob;
The DOB values do have to be consecutive in the data. I also have gender that I could use to make sure that there aren't two random people back to back that happen to have the same DOB.
In that case, you could use the program I posted with one change:
by GENDER DOB notsorted;
You would not need to sort your data first as long as consecutive matching records belong to the same person, and nonconsecutive records belong to different people.
Thank you for your help, it is very much appreciated!
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.