topic Create row based on another row in SAS Programming

Create row based on another row

mar0000 — Wed, 09 Jun 2021 20:01:47 GMT

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!!

Re: Create row based on another row

Reeza — Wed, 09 Jun 2021 20:25:14 GMT

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

Dataset with rows based on other rows

mar0000 — Wed, 09 Jun 2021 20:44:05 GMT

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

ID Race

1 White

2 Alaskan Native

3 African American

4 Native Hawaiian

I appreciate any help!!

Re: Dataset with rows based on other rows

Reeza — Wed, 09 Jun 2021 20:51:18 GMT

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

Re: Create row based on another row

mar0000 — Wed, 09 Jun 2021 20:58:45 GMT

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

Re: Create row based on another row

Reeza — Wed, 09 Jun 2021 21:25:19 GMT

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

Re: Create row based on another row

andreas_lds — Thu, 10 Jun 2021 06:11:31 GMT

How do you define "uncommon race"?