How to join 2 tables using 'or' condition on key fields?

[ncsthbell]

I am using SAS EG 4.2 and pretty new to it.  I am trying to use a query builder to join together 2 tables where the key can exist in one or more columns.

Example:

Data:

TableA - col1                  <-- if TableA.col1 value is in either one of the columns on TableB, I want to join the data, can't figure out how to accomplish this.

TableB - col1, col2

I need to join the TableA to TableB if TableA.col1 = (TableB.col1 or TableB.col2)

I tried to build the join for TableA.col1 = TableB.col1,  then I went into 'Edit' on the join properties and added 'OR TableA.col1 - TableB.col2)

I get syntax errors because it seems to be expecting the  operand to be 'AND', so what I wound up with wad:

TableA.col1 = TableB.col1 AND OR  TableA.col1 =  TableB.col2

Any suggestions would be greatly appreciated!!!

Thanks

[Linlin]

Hi,

there is a workaround. after run your joins, check the code EG generated:

PROC SQL;

   CREATE TABLE WORK.QUERY_FOR_ONE_0000 AS

   SELECT t1.col1,

          t2.col1 AS col11,

          t2.col2

      FROM WORK.ONE t1, WORK.TWO t2

      WHERE (t1.col1 = t2.col1 AND t1.col1 = t2.col2);

QUIT;

change the "AND" to "or",run the code again.

[ncsthbell]

Thank you Linlin.  I have not used SAS EG (4.2) to create my own code yet. I am using query builder (just learning EG).  Do I need to create a 'program' and put the code in it to run?

[Linlin]

Hi,

You don't have to create your own code, EG creates the code automaticlly when you use query builder(you can look at the code  by clicking "code" tab) ,you only need to modify the code. Please check the attached screenshot. Hope this helps.  - Linlin