How to merge two specific rows in the dataset

[heyyou1]

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.

[PaigeMiller]

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

[heyyou1]

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

[Reeza]

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.

---