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.
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
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
I appreciate your help Harry but I've mentioned two conditions if you closely observe it.
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
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.
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 .
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
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;
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!.
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
Expect no further answer from me if these four points have not been met.
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
Yes, I am but does that actually work?.
@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.
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!.
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
)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: