topic Re: Join table on an item in a set of columns in SAS Programming

Join table on an item in a set of columns

sarahzhou — Tue, 28 Jun 2022 14:15:27 GMT

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!

Re: Join table on an item in a set of columns

ballardw — Tue, 28 Jun 2022 15:26:07 GMT

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.

Re: Join table on an item in a set of columns

PGStats — Tue, 28 Jun 2022 19:09:58 GMT

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.

Re: Join table on an item in a set of columns

sarahzhou — Wed, 29 Jun 2022 15:14:55 GMT

Thanks