topic Re: Making if then statement grab multiple lines from dataset instead of just first one described. in SAS Programming

Making if then statement grab multiple lines from dataset instead of just first one described.

plantprion — Mon, 06 Aug 2018 13:09:47 GMT

I am trying to make a bit of code that will take a string from my dataset in excel and turn it into a number. However, there are some observations that have multiple groups and my code is only grabbing the first one it sees and I would like it to somehow recognize that there are multiple different groups in the same observations and classify it as other. Here is my example:

Have:

Where I want White to be coded as 1, African American to be coded as 2 etc and if there are two different races then I want 6 for other.

Here is my code for attempting to do this:

data race;
   input ID $ Race $;
   datalines;
1 White 
2 AfricanAmerican 
3 White/AfricanAmerican
4 Hispanic
5 Hispanic/AfricanAmerican
6 NativeAmerican
7 PacificIslander
8 Other
9 PacificIslander/NativeAmerican
10 Hispanic/White 
;

data race;
if find (Race, "White") ge 1 then code = 1;
else if find (Race, "AfricanAmerican") ge 1 then code = 2;
else if find (Race, "Hispanic") ge 1 then code = 3;
else if find (Race, "NativeAmerican") ge 1 then code = 4;
else if find (Race, "PacificIslander") ge 1 then code = 5;
else if find (Race, "Other") ge 1 then code = 6;
run;

Re: Making if then statement grab multiple lines from dataset instead of just first one described.

RW9 — Mon, 06 Aug 2018 12:47:11 GMT

This works:

data race;
  length race $200;
  input id $ race $;
  datalines;
1 White 
2 AfricanAmerican 
3 White/AfricanAmerican 
;
run;

data race;
  set race;
  if index(race, "White") then code = 1;
  if index(race, "AfricanAmerican") then code=2;
  if index(race,"White") and index(race,"AfricanAmerican") then code=3;
run;

Am using a couple of simplifications, index returns the first position of the word in the string or 0, so if its found then true. Also I use greatest wins, i.e. White would first become 1, then become 3 later on due to position of the if.

TBH I wouldn't do it this way. First step would be to split out the race value into component parts, e.g.:

array r{2} $200;
do i=1 to countw(race,"/");
  r{i}=scan(race,i,"/");
end;

Then you would apply a format to the races using a proc format. Its just easier to standardise that way.

Re: Making if then statement grab multiple lines from dataset instead of just first one described.

plantprion — Mon, 06 Aug 2018 12:53:20 GMT

Thank you for your help, the issue is that I have many more race variables then just the two so I did not want to just break up the two parts for every possible combination of races. For your second solution, I actually have no idea how that works so if you have any reading or can explain what you are doing in that second step that would be very helpful. Thanks again.

Re: Making if then statement grab multiple lines from dataset instead of just first one described.

RW9 — Mon, 06 Aug 2018 13:02:37 GMT

Present some accurate test data then, and show what you want the output to look like.

Re: Making if then statement grab multiple lines from dataset instead of just first one described.

plantprion — Mon, 06 Aug 2018 13:10:13 GMT

I edited my original post to be more clear in what I am working with and what I actually want.

Re: Making if then statement grab multiple lines from dataset instead of just first one described.

RW9 — Mon, 06 Aug 2018 13:20:20 GMT

So:

data race;
  length id race $200;
   input id $ race $;
   datalines;
1 White 
2 AfricanAmerican 
3 White/AfricanAmerican
4 Hispanic
5 Hispanic/AfricanAmerican
6 NativeAmerican
7 PacificIslander
8 Other
9 PacificIslander/NativeAmerican
10 Hispanic/White 
;
run;

data race;
  set race;
   if index(race,"White") then code = 1;
   if index(race,"AfricanAmerican") then code = 2;
   if index(race,"Hispanic") then code = 3;
   if index(race,"NativeAmerican") then code = 4;
   if index(race,"PacificIslander") then code = 5;
   if index(race,"Other") then code = 6;
run;

Re: Making if then statement grab multiple lines from dataset instead of just first one described.

Astounding — Mon, 06 Aug 2018 13:30:01 GMT

So do the combinations belong in code=6, or do they start a new category such as code=7? I'll use 7 for the sample code.

Starting with the first ELSE statement, turn each into two possibilities. I'll illustrate just one set of changes for one ELSE statement:

else if find (Race, "AfricanAmerican") then do;

if code=. then code=2;

else code=7;

end;

So each time you find a match, check whether CODE was already assigned by an earlier match.