☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## Critical joining

Hi Everyone,

I believe you guys are doing fine!. I require some assistance from you as I got stuck while doing joining.

Here's the explanation :

I've two tables T1, T2 and for T1 I've 3 columns named ItemNo, ItemC1, ItemC2 and for table T2 I've ItemNo, ItemC1 to ItemC10. Here, I want to find records where T1.ItemNo= T2.ItemNo and any ItemCode in T1 present in any ItemCode in T2. The final output should have the records of above met condition and the columns should be from table T2 only.

Much appreciated if anyone could help me on this.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Critical joining

You data steps aren't going to work as written. So first let's clean that up.  We don't need 10 variables to demonstrate the problem so let's cut that down to just 5.

``````data T1;
input ItemNo :\$10. (ItemC1 ItemC2) (:\$8.);
datalines;
A10  MRF Ceat
A100 MRF Ceat
B01  IRP .
B011 IRP CRO
;
data T2;
input ItemNo :\$10. (ItemC1 - ItemC5) (:\$8.);
datalines;
A101  MRF . . Ceat .
A100  MRF . Ceat . .
B01   IRP . . . CRO
B0101 IRP CRO . . .
;``````

It looks like you just want to find the observations in T1 that match so let's do an INNER JOIN.

``````proc sql;
create table want as
select a.*
from t1 a
inner join t2 b
on a.itemno=b.itemno
and (whichc(a.itemc1,b.itemc1,b.itemc2,b.itemc3,b.itemc4,b.itemc5)
or whichc(a.itemc2,b.itemc1,b.itemc2,b.itemc3,b.itemc4,b.itemc5)
)
;
quit;``````

Results

```       Item    Item    Item
Obs     No      C1      C2

1     A100    MRF     Ceat
2     B01     IRP

```
30 REPLIES 30
SAS Employee

## Re: Critical joining

Hi @Pandu2

If you only need items from T2 filtered for presence in T1 then rather than using a join you might want to consider a nested SQL query.

See a simple example below.

Thanks

Harry

Obsidian | Level 7

## Re: Critical joining

I appreciate your help Harry but I've mentioned two conditions if you closely observe it.

SAS Employee

## Re: Critical joining

Hi @Pandu2

Your question is not very clear. It would help if you put an example of what you have and what you want the output to look like with some dummy records.

Re-reading your question, this should still be achievable with nested SQL - you just use the output of the inner join on model as the filter.

This could be done as a single SQL statement but it would look messier.

Thanks

Harry

Obsidian | Level 7

## Re: Critical joining

Well, here's

```Data T1;
Input ItemNo \$10. ItemC1 \$8. ItemC2 \$8.;
Datalines;
A10  MRF Ceat
A100 MRF Ceat
B01  IRP  .
B011 IRP CRO
;
Run;
Data T2;
Input ItemNo \$10. ItemC1 - ItemC10 \$8.;
Datalines;
A101  MRF . . Ceat . . .
A100 MRF . Ceat
B01  IRP  . . . CRO
B0101 IRP CRO
;
Run;

Output: The output table simply have to have the records of (Condition1 T1.ItemNo = T2.ItemNo )) AND  ( Condition2  T1.ItemC1 = T2. (ItemC1 - ItemC10) ) AND ( Condition3 T1.ItemC2 = T2. (ItemC1 - ItemC10) ) ;. The output table should meet these conditions.
Here Condition1 & 2 means Any value of ItemC1, ItemC2 of Table T1 matches with Any value of ItemC1 to ItemC10 of Table T2 then output the columns from Table T2 for those matched records.

I hope I made this clear and if you still have doubts please let me know. Thankyou!.
```

an example.

Obsidian | Level 7

## Re: Critical joining

To put that output in a better way A100 for ItemNo of T1 matches with A100 for ItemNo of T2 AND MRF for ItemC1 of T1 matches with MRF for ItemC1 of T2 AND Ceat for ItemC2 of T1 matches with Ceat for ItemC3 of T2. Likewise the conditions should match.

Here values of ItemC1, C2 of Table T1 may found in any variables of T2 for instance, consider the above explanation Value for ItemC2 of T1 found in ItemC3 of T2. So, if any matches found fetch the values from T2 .

```Required Output :
ItemNo  ItemC1  ItemC2  ItemC3  ItemC4 ItemC5 upto ItemC10
A100     MRF        .    Ceat     .      .            .                 .
B01      MRF        .     .       .     Ceat          .```
SAS Employee

## Re: Critical joining

How's this? The issue is that you need to search across ItemC1-ItemC10. In this simple approach I transpose T2 so that you get all ItemC1-C10 per ItemNo in a single column, then use that as a nested query.

``````/* example data */
Data T1;
INFILE DATALINES DSD;
Input ItemNo \$ ItemC1 \$ ItemC2 \$;
Datalines;
"A10" , "MRF" , "Ceat"
"A100" , "MRF" , "Ceat"
"B01" , "IRP" , ""
"B011" , "IRP" , "CRO"
;

Data T2;
INFILE DATALINES DSD;
Input ItemNo \$ (ItemC1 - ItemC10) (\$);
Datalines;
"A101", "MRF", "", "", "Ceat", "", "", "", "", "", ""
"A100", "MRF", "", "Ceat" , "" , "" , "", "", "", "",""
"B01",  "IRP" , "" ,"", "", "CRO","","","","",""
"B0101", "IRP", "CRO" ,"","","","","","","",""
;

/* Transpose columns in T2 */
proc sort data=WORK.T2 out=WORK.SORTTempTableSorted;
by ItemNo;
run;

proc transpose data=WORK.SORTTempTableSorted out=work.Transpose prefix=Column;
var ItemC1 ItemC2 ItemC3 ItemC4 ItemC5 ItemC6 ItemC7 ItemC8 ItemC9 ItemC10;
by ItemNo;
run;

proc sql; select * from transpose where column1 ne '';quit;

/* filter condition on list and itemno */
proc sql; select t2.* from t1,t2
where
t1.itemno = t2.itemno and
t1.itemc1 in (select column1 from transpose where itemno = t1.itemno);quit;``````

Hope this helps

Super User

## Re: Critical joining

B01 does not have the value MRF anywhere; I guess this is a typo. You should also learn basic DATA step coding and how to test and debug simple steps.

``````Data T1;
Input ItemNo :\$10. ItemC1 :\$8. ItemC2 :\$8.;
Datalines;
A10  MRF Ceat
A100 MRF Ceat
B01  IRP  .
B011 IRP CRO
;

Data T2;
infile datalines truncover;
Input ItemNo :\$10. (ItemC1 - ItemC10) (:\$8.);
Datalines;
A101  MRF . . Ceat . . .
A100 MRF . Ceat
B01  IRP  . . . CRO
B0101 IRP CRO
;

proc sort data=t1;
by itemno;
run;

proc sort data=t2;
by itemno;
run;

data want;
merge
t1 (in=t1)
t2 (in=t2 rename=(itemc1=_itemc1 itemc2 = _itemc2))
;
by itemno;
if t1 and t2;
array t2c {*} _itemc1 _itemc2 itemc3-itemc10;
do i = 1 to dim(t2c);
if itemc1 = t2c{i} then f1 = 1;
if itemc2 = t2c{i} then f2 = 1;
end;
if (f1 or itemc = "") and (f2 or itemc2 = "") then output;
drop _itemc1 _itemc2 i f1 f2;
run;``````
Obsidian | Level 7

## Re: Critical joining

I really appreciate your help. However, your code is just only matching the T1 ItemNo with T2 ItemNo and providing the values of ItemC1 - ItemC10 of Table T2 and not looking for the match of T1 ItemC1, ItemC2 across ItemC1 - ItemC10 of Table T2.

Besides match of T1 ItemNo with T2 ItemNo, it should also have a match of T1 ItemC1, ItemC2 in any of the columns of Table T2 (i.e T2 (ItemC1 - ItemC10 )). Once both the Conditions have a match then it should output the values of Table T2 for those matches. I hope I'm clear with this. Thanks!.

Super User

## Re: Critical joining

My code creates the exact output dataset from your own posting, with the exception of the value for B01 as mentioned.

If you need something different, you will

• post example data in  WORKING data steps
• include enough cases for all possibilities
• post the exact expected result form this
• clearly define the rules

Expect no further answer from me if these four points have not been met.

Super User

## Re: Critical joining

You are using some sort of of short hand to describe a condition that needs serious expansion.

`T1.ItemC1 = T2. (ItemC1 - ItemC10)`

Are you aware of the IN operator? It will return true if a value is found in a list, so I think you may be looking for:

t1.itemc1 IN (t2.itemc1 t2.itemc2 t2.itemc3 .... t2.itemc10)

OR

t1.itemc2 IN (t2.itemc1 t2.itemc2 t2.itemc3 .... t2.itemc10)

to select records

Obsidian | Level 7

## Re: Critical joining

Yes, I am but does that actually work?.

Super User

## Re: Critical joining

@Pandu2 wrote:

Yes, I am but does that actually work?.

Have you tried it?

Join ON conditions just need to resolve to true or false.

Don't like the verbosity needed for SQL so am not going to write out all the variable names.

Obsidian | Level 7

## Re: Critical joining

I've tried it but didn't work out.

Here's my code and please suggest me if I made any logical error here.

```Proc SQL;
Create table xyz as
Select * from T2
Inner join T1
On T1.ItemNo = T2.ItemNo
And
T1. ItemC1 IN ( Select T2.ItemC1 T2.ItemC2  T2.ItemC3 T2.ItemC4 T2.ItemC5 T2.ItemC6 T2.ItemC7 T2.ItemC8 T2.ItemC9 T2.ItemC10 from T2)
OR
T1. ItemC2 IN ( Select T2.ItemC1 T2.ItemC2  T2.ItemC3 T2.ItemC4 T2.ItemC5 T2.ItemC6 T2.ItemC7 T2.ItemC8 T2.ItemC9 T2.ItemC10 from T2);
Quit;

This code is causing me errors bcuz can we select more than one column in a nested SQL Query?. Please help me on this. Thankyou!.
```
Super User

## Re: Critical joining

So this

``````T1. ItemC1 IN
( Select T2.ItemC1 T2.ItemC2  T2.ItemC3 T2.ItemC4 T2.ItemC5 T2.ItemC6 T2.ItemC7 T2.ItemC8 T2.ItemC9 T2.ItemC10 from T2)``````

is just not valid SQL in so many ways.

The IN operator in SAS want VALUES in the list, not references to VARIABLES.

But the WHICHN() or WHICHC() operators can use variables in the list.

In normal SAS code you could use variable list (once you have given them all unique names):

``whichc(T1_ItemC1, of  t2_itemc1-t2_itemc10)``

But in SQL code you have to actually type out all of the variables (and also the commas that SQL insists on using all over the place).

``````whichc(T1.ItemC1 ,t2.itemc1 ,t2.itemc2 ,t2.itemc3 ,t2.itemc4 ,t2.itemc5
,t2.itemc6 ,t2.itemc7 ,t2.itemc8 ,t2.itemc9 ,t2.itemc10)``````

If you want to do what you attempted in SQL you would need a lot of UNIONs.

``````T1. ItemC1 IN
( select ItemC1 from T2
union select ItemC2 from T2
union select ItemC3 from T2
union select ItemC4 from T2
union select ItemC5 from T2
union select ItemC6 from T2
union select ItemC7 from T2
union select ItemC8 from T2
union select ItemC9 from T2
union select ItemC10 from T2
)``````

Discussion stats
• 30 replies
• 2053 views
• 0 likes
• 6 in conversation