Create row based on another row

[mar0000]

I have a dataset with race and ID:

ID        Race      

1           White

1           Declined to Answer

2           Alaskan Native   

3           Unknown       

3           African American             

4           White

4           Native Hawaiian

I would like for the dataset to have one ID and one race. If the race is 'Unknown' or 'Declined to Answer' I would like to have Race be valued as the the race that is known (White for ID #1 and African American for ID #3). If there are two races valued, I would like the value to be the more uncommon race (Native Hawaiian for ID #4). I would ideally like to have a dataset that looks like:

ID        Race      

1           White

2           Alaskan Native   

3           African American             

4           Native Hawaiian

I appreciate any help!!

[Reeza]

1. Recode Unknown/Declined to Answer to a SAS missing value or special missing if you still want to uniquely identify the records.
2. Use PROC FREQ to create a ranking of the races
3. Merge the rank data with the actual data - including the rank
4. Sort your data now by rank from #3, take the first record per ID

[mar0000]

Can you provide some example code? This explanation isn't very clear to me. Thank you

[Reeza]

Please clarify - which portion is not clear? What did you try so far and what did you get? Please include any code and log.

[mar0000]

I have a dataset with race and ID:

ID        Race      

1           White

1           Declined to Answer

2           Alaskan Native   

3           Unknown       

3           African American             

4           White

4           Native Hawaiian

I would like for the dataset to have one ID and one race. If the race is 'Unknown' or 'Declined to Answer' I would like to have Race be valued as the the race that is known (White for ID #1 and African American for ID #3). If there are two races valued, I would like the value to be the more uncommon race (Native Hawaiian for ID #4). I would ideally like to have a dataset that looks like:

ID        Race      

1           White

2           Alaskan Native   

3           African American             

4           Native Hawaiian

I appreciate any help!!

[Reeza]

FYI - I merged your posts as they're identical. If you have a reason for reposting please let us know.

[andreas_lds]

How do you define "uncommon race"?