Using 'if' statement with 'and' in an array

[Angmar]

Hello,

I'm trying to use the following code on a set of variables (12 variables) to identify records where the respondent answered '1' '2' and '5' in any of the 12 fields, but it is not working (the new variable, 'scenario_5_new' comes up with '.' missing for each record). I think it has something to do with the 'and' operator but I'm not sure.

Thank you -

DATA MYDATA.R2

SET MYDATA.R2;
ARRAY DX (12)  Q18M1-Q18M12;
DO i = 1 TO 12;
IF DX(i) = '1' and DX(i) = '2' and DX(i)= '5' THEN scenario_5_new = 1;
END;
RUN;

[Reeza · Accepted solution]

DATA MYDATA.R3

SET MYDATA.R2;
ARRAY DX (12)  Q18M1-Q18M12;

if whichc('1', of Dx(*)) and whichc('2', of dx(*)) and whichc('5', of dx(*)) then  scenario_5_new = 1;

RUN;

That AND criteria will never be true as a variable can only ever have one value at a time.

If you need to check for all three values, it's easier to use WHICHC.

If your responses are always single digit there are some other methods to check if 1/2/5 are in the list as well. 

Basically concatenate all responses, remove anything not a 1/2/5 via COMPRESS and see if the values is equal to 1/2/5. 

[Angmar]

is there a wildcard function or something that I can add to this so that the code captures records with 1, 2, and 5 even if there are other responses in the other fields (e.g., 3, 1, 7, 2, 5)?

[ballardw]

---

@Angmar wrote:
is there a wildcard function or something that I can add to this so that the code captures records with 1, 2, and 5 even if there are other responses in the other fields (e.g., 3, 1, 7, 2, 5)?

---

Did you test @Reeza's code  with your data a see if it did not do exactly that?

The way Whichc, and its numerical equivalent Whichn, works is that it compares the first parameter to a list of values, in this case an array, and if a match is found returns the list position of the first match evaluated from left to right in the list or a 0 (zero) if no match is found.

SAS will treat the returned position numbers as "True" if other than 0. So the "And" are now comparing actual true/false results as 
"Is a 1 found" And "is a 2 found" and "is a 5 found". If all three are found then the indicator is set.

There is nothing in Reeza's code that says "if some value is 3 (or  7) then don't set the indicator to 1.

[Tom]

The IN operator can work with an array.

DATA MYDATA.R2
  SET MYDATA.R2;
  ARRAY DX  Q18M1-Q18M12;
  scenario_5_new = ('1' in dx) and ('2' in dx) and ('5' in dx);
RUN;

[Angmar]

Thank you - these codes are both working, but only selecting records where 1,2,5 are the only responses. If there are responses in the other 9 fields (12 variables are in the array), these records aren't selected. I need all records where 1, 2, and 5 are responses within the 12 variable set, regardless of whether there are additional responses that are not 1,2, and 5. 

I hope I am being clear. I appreciate everyone's help so far.

[Reeza]

That should not happen with the code posted. Please post the exact code you're running.

---

@Angmar wrote:

Thank you - these codes are both working, but only selecting records where 1,2,5 are the only responses. If there are responses in the other 9 fields (12 variables are in the array), these records aren't selected. I need all records where 1, 2, and 5 are responses within the 12 variable set, regardless of whether there are additional responses that are not 1,2, and 5. 

I hope I am being clear. I appreciate everyone's help so far.

---

[Angmar]

Thank you - you are correct. There was an issue with one of my variables that wasn't related to the code.

Thank you everyone!