Join table on an item in a set of columns

[sarahzhou]

Hi, 

I need to join a table when an item is in a set of the three columns:

Master list (the full table is thousands entries) table A

id1	id2	id3	item_key	item_name	manufacture_date	expiry_date
S123	F123	G111	A123	APPLE	1-Jan-2020	1-Jun-2021
S124	F234	G345	A234	BERRY	2-Jan-2020
S125	F345	F234	A345	CARROR	3-Jan-2020
S126	F456	G813	A456	DIAMOND	4-Jan-2020

each id number is distinct in a column.

and a purchase list (actual list is thousands entries) table B:

required_ item

F234

S123

F234

G813

Expected output table

item_key	required item	item_key	manufacture_date	expiry_date
A234	F234	BERRY	2-Jan-2020
A123	S123	APPLE	1-Jan-2020	1-Jun-2021
A345	F234	CARROR	3-Jan-2020
A456	G813	DIAMOND	4-Jan-2020

the required_item needs to be in the output for cross referencing.

I've tried run left join on B.required_item=A.ID1 OR B.required_item=A.ID2 OR B.required_item=A.ID3, however it was taking very long  to run. 

Does sas has a function to extract an entry when one is in the set, for this example when B.required_item in  set (A.ID1,A.ID2,A.ID3)?

Kindly advise.

Thank you!

[ballardw]

Take the first set and make it so that you have ONE "id" variable per observation and the same key, name and date variables. Yes it will be bigger.

It will also allow a simple  SQl "join on" to match your "required_item" data.

If your "master" data does not change often then this is likely a more robust approach in general than attempting to search multiple variables. One join instead of 3 should run quicker.

[PGStats · Accepted solution]

SAS SQL optimizer does poorly on joins involving OR conditions. Joining

on whichc(B.required_item, A.id1, A.id2, A.id3) ne 0

might run faster.

[sarahzhou]

Thanks