Join using OR function as a criteria

[janus21]

Hi all,

I have data that looks like the table below that I would like to join onto another table.

Criteria1	Criteria2	Criteria3 (OR)	Result
X	Y	1, 2, 3, 4, 5	"Hello"

The intended process is to check for the following:

Criteria1 matches AND Criteria2 matches AND one of the Criteria3 entries match when delimited by comma.

eg. 

X and Y and 1, Result = "Hello"

X and Y and 2, Result = "Hello"

etc.

Is there a way to do this in SAS? I have an idea below but it's not the prettiest. Any help would be appreciated!

Currently, I have imported the criterion table in and am planning to manipulate the data to,

1. Delimit Criteria3 column based off comma into separate columns.

2. Create column joining all criteria, delimited Criteria3 columns and expected result, eg. Created_col1, X/Y/1/"Hello"; Created_col2, X/Y/2/"Hello"; etc.

3. Transpose data

4. Separate result so end data is

Criteria	Result
X/Y/1	"Hello"
X/Y/2	"Hello"
etc

5. Use put( formula to join Result on

[PaigeMiller]

You don't show the second table. Please show us a portion of the second table, more than one record.

Please create a more realistic example of the first table where there is more than one record to match.

Please show us a realistic example where some records don't match.

[Tom]

By "X" and "Y" do you actually mean some boolean expression.  Or do you mean literal values to be compared.  If the latter what variable are you going to compare these values to?  Can you provide more concrete examples?

Are you asking for help in understanding how to manually use the criteria?

Or do already know how to do it (if so then show what code you want to run to perform the first record) and just need help converting the data in that table into SAS code to execute.

[ballardw]

I suspect you don't have much experience with logical comparisons in general and maybe less in asking questions.

In SAS a simple statement like "if X and Y and 1" evaluates to true if X is a numeric value other than 0 or missing, Y is a numeric value other than 0 or missing. 1 is not 0 or missing so is true.

If you mean: X is one of  the values in the list (1,2,3,4,5) and Y is one of the values in the list (1,2,3,4,5) then the way you have displayed this is very wrong because a value of X is never going to be one of those and a value of Y is never going to be one of those.

The operator "and" must appear between numeric values or expressions that result in a numeric value (SAS treats numbers other than 0 and missing as true, 0 and missing as false).

You can use the IN operator to determine if a variable or expression result matches the values in a list such as (x - y) in (1,2,3,4,5).

---

@janus21 wrote:

Hi all,

I have data that looks like the table below that I would like to join onto another table.

Criteria1	Criteria2	Criteria3 (OR)	Result
X	Y	1, 2, 3, 4, 5	"Hello"

The intended process is to check for the following:

Criteria1 matches AND Criteria2 matches AND one of the Criteria3 entries match when delimited by comma.

eg. 

X and Y and 1, Result = "Hello"

X and Y and 2, Result = "Hello"

etc.

Is there a way to do this in SAS? I have an idea below but it's not the prettiest. Any help would be appreciated!

Currently, I have imported the criterion table in and am planning to manipulate the data to,

1. Delimit Criteria3 column based off comma into separate columns.

2. Create column joining all criteria, delimited Criteria3 columns and expected result, eg. Created_col1, X/Y/1/"Hello"; Created_col2, X/Y/2/"Hello"; etc.

3. Transpose data

4. Separate result so end data is

Criteria	Result
X/Y/1	"Hello"
X/Y/2	"Hello"
etc

5. Use put( formula to join Result on

---

[ChrisNZ]

Do you mean 

if CRITERIA1='X' and 'CRITERIA2='Y' and CRITERIA3 in(1, 2, 3, 4, 5) then RESULT='Hello';

or

if CRITERIA1='X' and 'CRITERIA2='Y' and CRITERIA3 in(1, 2, 3, 4, 5) then do;
  CRITERIA=catx(',', CRITERIA1, CRITERIA2, CRITERIA3);
  RESULT='Hello';
end;