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

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
Tom
Super User Tom
Super User

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

View solution in original post

30 REPLIES 30
HarrySnart
SAS Employee

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.

 

HarrySnart_0-1676545252719.png

 

Thanks

Harry

Pandu2
Obsidian | Level 7

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

 

HarrySnart
SAS Employee

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.

 

HarrySnart_0-1676550215021.png

Thanks

Harry

Pandu2
Obsidian | Level 7

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
Obsidian | Level 7

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
SAS Employee

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
Super User

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
Obsidian | Level 7

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
Super User

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
Super User

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
Obsidian | Level 7

Yes, I am but does that actually work?.

ballardw
Super User

@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
Obsidian | Level 7

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
Super User Tom
Super User

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
)

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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