Reclassifying Categorical data within a Group

OlsabeckT29 — Mon, 18 Dec 2023 13:59:32 GMT

I have a long dataset of over a million records of people with their race category. Some people have more than one race assigned to their person id. What I want to do is select the best race for each person id. In the dataset some people will have an observation that says unknown or other and also actual race category, I want to get rid of the unknown/other observation and keep the more descriptive race category. Some people will also have different races listed. For example person A will have one observation that says black and one observation that says white. For those I want to be able to assign that person's race to be multiracial. I was trying to do this using a Do Until statement but it is not outputting what I want.

proc sort data=race_check3; ; by child_id; run;

Data race_check4;
Do until (last.child_id);
	set race_check3;
	by child_id;
	if (race_eth NE 'Unknown') then race_eth2=race_eth;
end;
run;

Data have:

Child_id	Race_eth
16849918029	NH Asian/Native Hawaiian/Other Pacific Islands
16849918029	NH White
16850191831	NH White
16850191831	Unknown
16850404097	NH Asian/Native Hawaiian/Other Pacific Islands
16850404097	Unknown
16850502704	NH Asian/Native Hawaiian/Other Pacific Islands
16850502704	NH White
16850783900	Other
16850783900	Unknown
16850922663	NH Asian/Native Hawaiian/Other Pacific Islands
16850922663	Unknown
16851203403	NH Asian/Native Hawaiian/Other Pacific Islands
16851203403	NH White
16851234942	NH White
16851234942	Unknown

Data Want

Child_id	Race_eth
16849918029	Multi Racial
16850191831	NH White
16850404097	NH Asian/Native Hawaiian/Other Pacific Islands
16850502704	Multi Racial
16850783900	Other
16850922663	NH Asian/Native Hawaiian/Other Pacific Islands
16851203403	Multi Racial
16851234942	NH White

Re: Reclassifying Categorical data within a Group

s_lassen — Mon, 18 Dec 2023 14:35:02 GMT

Something like this, perhaps:

Data race_check4;
  Do until (last.child_id);
    set race_check3;
    by child_id;
	length race_eth2 $100;
    if (race_eth NE 'Unknown') then do;
	  if race_eth2=' ' then
        race_eth2=race_eth;
      else if race_eth2 ne race_eth then
	    race_eth2='Multi Racial';
	  end;
    end;
  if race_eth2=' ' then
    race_eth2='Unknown';
  drop race_eth;
  rename race_eth2=race_eth;
run;

topic Reclassifying Categorical data within a Group in SAS Programming

Reclassifying Categorical data within a Group

Re: Reclassifying Categorical data within a Group