topic How to merge two specific rows in the dataset in New SAS User

How to merge two specific rows in the dataset

heyyou1 — Tue, 23 Nov 2021 18:15:12 GMT

Hello, first off thanks for your help. The SAS community has been very helpful so far.

I have data that looks like this

County | Region | FIPS | Race | Applied Count
"Cool County" | 1 | 10101 | Asian | 5
"Cool County" | 1 | 10101 | Black or African American| 50
"Cool County" | 1 | 10101 | Other Race| 20
"Cool County" | 1 | 10101 | Unknown | 8
"Cool County" | 1 | 10101 | White | 30

I then have census population data that looks like this:

County | Region | FIPS | Race | Population
"Cool County" | 1 | 10101 | Asian | 6000
"Cool County" | 1 | 10101 | Black or African American| 35000
"Cool County" | 1 | 10101 | Other Race/ Unknown | 18000
"Cool County" | 1 | 10101 | White | 200000

I would like to combine the tables into something like this, so I can understand what percentage of the population applied.

"Cool County" | 1 | 10101 | Asian | 5 | 6000
"Cool County" | 1 | 10101 | Black or African American| 50 | 35000
"Cool County" | 1 | 10101 | Other Race/ Unknown | 28 | 18000
"Cool County" | 1 | 10101 | White | 30 | 200000

How do I merge the "unknown" and "other race" rows in the Application Count table to better merge with the census data?

All other rows line up and have a match and will have a match in the future. It's just the oddity of the "unknown" and "other race" rows.

Re: How to merge two specific rows in the dataset

PaigeMiller — Tue, 23 Nov 2021 18:32:02 GMT

You could use a text editor to globally change

Other Race/ Unknown

into

Other Race

Or you could do the same thing in a SAS data step

Re: How to merge two specific rows in the dataset

heyyou1 — Tue, 23 Nov 2021 18:39:40 GMT

How would I do that with a data step while summing "Applied count" for both of them?

Re: How to merge two specific rows in the dataset

Reeza — Tue, 23 Nov 2021 18:59:03 GMT

For your first data set:

data recoded;
set table1;
if race  in ( 'Other Race', Unknown') then race = 'Other Race/Unknown';
run;

proc means data=recoded noprint SUM;
by county region fips race;
var applied_count;
ods output summary = summarized;
run;

Then do a standard match merge process of your choice.

@heyyou1 wrote:

Hello, first off thanks for your help. The SAS community has been very helpful so far.

I have data that looks like this
County | Region | FIPS | Race | Applied Count
"Cool County" | 1 | 10101 | Asian | 5
"Cool County" | 1 | 10101 | Black or African American| 50
"Cool County" | 1 | 10101 | Other Race| 20
"Cool County" | 1 | 10101 | Unknown | 8
"Cool County" | 1 | 10101 | White | 30
I then have census population data that looks like this:
County | Region | FIPS | Race | Population
"Cool County" | 1 | 10101 | Asian | 6000
"Cool County" | 1 | 10101 | Black or African American| 35000
"Cool County" | 1 | 10101 | Other Race/ Unknown | 18000
"Cool County" | 1 | 10101 | White | 200000
I would like to combine the tables into something like this, so I can understand what percentage of the population applied.

County | Region | FIPS | Race | Applied Count | Population
"Cool County" | 1 | 10101 | Asian | 5 | 6000
"Cool County" | 1 | 10101 | Black or African American| 50 | 35000
"Cool County" | 1 | 10101 | Other Race/ Unknown | 28 | 18000
"Cool County" | 1 | 10101 | White | 30 | 200000
How do I merge the "unknown" and "other race" rows in the Application Count table to better merge with the census data?

All other rows line up and have a match and will have a match in the future. It's just the oddity of the "unknown" and "other race" rows.