Critical joining

[Pandu2]

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.

[HarrySnart]

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

[Pandu2]

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

[HarrySnart]

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

[Pandu2]

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. 

[Pandu2]

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          .

[HarrySnart]

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

[Kurt_Bremser]

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;

[Pandu2]

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!.

[Kurt_Bremser]

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.

[ballardw]

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

[Pandu2]

Yes, I am but does that actually work?.

[ballardw]

---

@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.

[Pandu2]

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!.

[Tom]

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
)