05-03-2012 04:16 PM
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.
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!!!
05-03-2012 06:54 PM
there is a workaround. after run your joins, check the code EG generated:
CREATE TABLE WORK.QUERY_FOR_ONE_0000 AS
t2.col1 AS col11,
FROM WORK.ONE t1, WORK.TWO t2
WHERE (t1.col1 = t2.col1 AND t1.col1 = t2.col2);
change the "AND" to "or",run the code again.
05-04-2012 08:10 AM
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?
05-04-2012 10:19 AM
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