topic Re: identifying rows from the same family and adding a variable for their order of age in SAS Programming

identifying rows from the same family and adding a variable for their order of age

Emma_at_SAS — Thu, 05 Oct 2023 16:10:47 GMT

I have a national father and kids dataset and want to create a variable FAMILY to show the members of the same family. For example, if POSTALCODE, CITY, STATE, and LAST NAME observations are the same then FAMILY = j (where j would be the number of families in my dataset); also I want to sort the members of the same family from older age to younger age. For example, FAMILY = 1 and FAMILY_ORDER = 1 for the father, FAMILY_ORDER = 2 for the oldest kid, FAMILY_ORDER = 3 for the next kid and so on to cover all members of the same family. How may I create these two variables?

Thanks

Re: identifying rows from the same family and adding a variable for their order of age

PaigeMiller — Thu, 05 Oct 2023 16:45:43 GMT

It would be helpful if you could show us a portion of this data set (perhaps with made up family names). Can we assume that all the people in one family are consecutive in the data set, or are they scattered throughout the data set?

Re: identifying rows from the same family and adding a variable for their order of age

Emma_at_SAS — Thu, 05 Oct 2023 16:47:53 GMT

Thanks, @PaigeMiller for your thoughts. The family members are scattered in the data.

Re: identifying rows from the same family and adding a variable for their order of age

PaigeMiller — Thu, 05 Oct 2023 16:49:33 GMT

So if there are multiple families named SMITH living in the same postalcode, how could we tell them apart?

Re: identifying rows from the same family and adding a variable for their order of age

Emma_at_SAS — Thu, 05 Oct 2023 16:53:21 GMT

Good question @PaigeMiller . There is more specific information, phone number (the father reported his phone number for the kids), email address of the father for all family members, and race.

Re: identifying rows from the same family and adding a variable for their order of age

PaigeMiller — Thu, 05 Oct 2023 16:58:51 GMT

Without seeing your data, here's a guess

1. Sort by Family Name, Phone Number and descending age

2. Each time the phone number changes, add 1 to the family number

Re: identifying rows from the same family and adding a variable for their order of age

ballardw — Thu, 05 Oct 2023 17:41:32 GMT

@Emma_at_SAS wrote:

Good question @PaigeMiller . There is more specific information, phone number (the father reported his phone number for the kids), email address of the father for all family members, and race.

Be very careful assuming phone numbers or email addresses do not change.

Also a potential headache with father/child data is if the father may be attempting to skip out on support then name changes might pop-up depending on the data sources. "Robert" may become "Bob" or "Bobbie" or similar. Or change to use of a middle name in stead of first.