topic Re: Critical joining in SAS Programming

Critical joining

Pandu2 — Thu, 16 Feb 2023 12:28:36 GMT

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.

Re: Critical joining

HarrySnart — Thu, 16 Feb 2023 11:01:00 GMT

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

Re: Critical joining

Pandu2 — Thu, 16 Feb 2023 11:07:31 GMT

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

Re: Critical joining

HarrySnart — Thu, 16 Feb 2023 12:24:17 GMT

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

Re: Critical joining

Pandu2 — Thu, 16 Feb 2023 12:53:56 GMT

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.

Re: Critical joining

Pandu2 — Thu, 16 Feb 2023 13:25:09 GMT

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          .

Re: Critical joining

HarrySnart — Thu, 16 Feb 2023 14:11:40 GMT

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

Re: Critical joining

ballardw — Thu, 16 Feb 2023 16:17:17 GMT

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)

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

to select records

Re: Critical joining

Pandu2 — Thu, 16 Feb 2023 16:32:17 GMT

Yes, I am but does that actually work?.

Re: Critical joining

ballardw — Thu, 16 Feb 2023 16:52:01 GMT

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

Re: Critical joining

Pandu2 — Fri, 17 Feb 2023 04:17:34 GMT

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

Re: Critical joining

Tom — Mon, 20 Feb 2023 18:28:39 GMT

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
)

Re: Critical joining

SASKiwi — Mon, 20 Feb 2023 22:14:43 GMT

Maybe a DATA step approach is better. It does require sorting of the two input tables though. Try this:

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" ,"","","","","","","",""
;

proc sort data = T1;
  by ItemNo;
run;

proc sort data = T2;
  by ItemNo;
run;

data Want;
  keep ItemNo T1ItemC1 T1ItemC2 ItemC1 - ItemC10;
  retain T1ItemC1 T1ItemC2;
  set T1 T2;
  by ItemNo;
  array T2 (*) $ ItemC1 - ItemC10;
  if not (first.ItemNo and last.ItemNo) then do;
    if first.ItemNo then do;
	  T1ItemC1 = ItemC1; T1ItemC2 = ItemC2;
	end;
    else do i = 1 to dim(T2);
	  if T1ItemC1 = T2(i) then ItemC1_Match = 'Y';
      if T1ItemC2 = T2(i) then ItemC2_Match = 'Y';
	end;
	if ItemC1_Match = 'Y' and ItemC2_Match = 'Y' then output;
  end;
run;

Re: Critical joining

Pandu2 — Tue, 21 Feb 2023 06:09:20 GMT

Thanks for your help. However, your code didn't provide me right results.

Re: Critical joining

Kurt_Bremser — Tue, 21 Feb 2023 15:01:27 GMT

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;

Re: Critical joining

Tom — Tue, 21 Feb 2023 15:56:43 GMT

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

Re: Critical joining

Pandu2 — Tue, 21 Feb 2023 16:02:36 GMT

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

Re: Critical joining

Pandu2 — Wed, 22 Feb 2023 03:51:45 GMT

I'm very much thankful for responding. However, the output table what should look like is :

Obs ItemNo  ItemC1 ItemC2 ItemC3 ItemC4 ItemC5 
1    A100    MRF      .    CEAT 
2    B01     IRP      .     .      .      .


This is how my output should look like, if the value of ItemC1, ItemC2 of T1 matches with any column of T2 then, it should output those matched values in whatever place they exist in T2.

Re: Critical joining

Tom — Tue, 21 Feb 2023 16:17:04 GMT

So you want the records from T2 instead of the records from T1.

That should be TRIVIAL to fix.

Did you try to fix it?
What happened.

Re: Critical joining

Kurt_Bremser — Tue, 21 Feb 2023 16:20:55 GMT

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.