topic Re: How to categorize the data value in multiple column to create new catergory in New SAS User

How to categorize the data value in multiple column to create new catergory

tsureshinvites — Wed, 02 Oct 2019 10:50:31 GMT

I need output for the below yellow last column like this. Thanks in advance.

ID	Race1	Race2	Race3	Race4	Output
1001	asian	hispanic	white		Mutiple
1002					"Missing"
1003	asian				asian
1004			Asian	Hispani	Mutiple

Re: How to categorize the data value in multiple column to create new catergory

PeterClemmensen — Wed, 02 Oct 2019 11:16:31 GMT

Hello @tsureshinvites and welcome to the SAS Community 🙂

You can do something like this

data have;
input ID (Race1-Race4)(:$10.);
infile datalines dsd dlm=',';
datalines;
1001,asian,hispanic,white,
1002,,,,
1003,asian,,,
1004,,,Asian,Hispanic
;

data want(drop=a);
    set have;
    a=cmiss(of Race:);
    if a in (1, 2) then output='Multiple';
    else if a=3    then output=coalescec(of Race:);
    else if a=4    then output='Missing'; 
run;

Result:

ID    Race1    Race2    Race3    Race4     Output
001   asian    hispanic white              Multiple
1002                                       Missing
1003  asian                                asian
1004           Asian             Hispanic  Multiple

Re: How to categorize the data value in multiple column to create new catergory

Reeza — Wed, 02 Oct 2019 15:05:27 GMT

array _race(*) race1-race4;
n_values = dim(_race) - cmiss(of _race(*));

if n_values > 1 then output = 'Multiple';
else if n_values = 1 then output = coalescec(of _race(*));
else if n_values = 0 then output = 'Missing';
else output = 'CHECKME';

Does't matter how many race variables you have with this version.

Use DIM() to get number of values, CMISS() to find number of missing and then the difference is how many values are filled in.

Re: How to categorize the data value in multiple column to create new catergory

Ksharp — Thu, 03 Oct 2019 14:00:45 GMT

data have;
input ID (Race1-Race4)(:$10.);
infile datalines dsd dlm=',';
datalines;
1001,asian,hispanic,white,
1002,,,,
1003,asian,,,
1004,,,Asian,Hispanic
;
data want;
 if _n_=1 then do;
  length key flag $ 40;
  declare hash h();
  h.definekey('key');
  h.definedone();
 end;
 set have;
 h.clear();
 array x{*} $ race:;
 do i=1 to dim(x);
   if not missing(x{i}) then do;key=x{i};h.ref();end;
 end;
 n=h.num_items;
 if n=0 then flag='Missing';
  else if n=1 then flag=coalescec(of x{*});
   else flag='Multiple';
 run;
 proc print;run;