Map two tables for the same set members

[sarahzhou]

I have two tables:

Orders:

Order_ID	Order_DT	Ship_ID	Ship_DT	Prod_ID	ProdExP_DT
O1	2022-01-01	S1	2022-01-01	P1	2022-01-30
O1	2022-01-01	S1	2022-01-01	P2	2022-02-28
O2	2022-01-02	S2	2022-01-02	P3	2022-03-01
O3	2022-01-03	S3	2022-01-03	P4	2022-03-04

Products:

Prod_ID	HasMembers	ProdEP_DT
P1	S1	2022-01-30
P1	O1	2022-01-30
P1	P2	2022-01-30
P2	S1	2022-02-28
P2	O1	2022-02-28
P2	P1	2022-02-28
P3	S2	2022-03-01
P4	S3	2022-03-04
P4	O3	2022-03-04

I want to check if the table has orders, ships and products mapped.

So, I want Orders_Map be

Order_ID	Order_DT	Ship_ID	Ship_DT	Prod_ID	ProdEP_DT	Member_Set
O1	2022-01-01	S1	2022-01-01	P1	2022-01-30	(O1,S1,P1,P2)
O1	2022-01-01	S1	2022-01-01	P2	2022-02-28	(O1,S1,P1,P2)
O2	2022-01-02	S2	2022-01-02	P3	2022-03-01	(O2,S2,P3)
O3	2022-01-03	S3	2022-01-03	P4	2022-03-04	(O3,S3,P4)

And, Products_Mapped be

Prod_ID	HasMembers	ProdEP_DT	Member_Set	Map_IND
P1	S1	2022-01-30	(O1,S1,P1,P2)	Y
P1	O1	2022-01-30	(O1,S1,P1,P2)	Y
P1	P2	2022-01-30	(O1,S1,P1,P2)	Y
P2	S1	2022-02-28	(O1,S1,P1,P2)	Y
P2	O1	2022-02-28	(O1,S1,P1,P2)	Y
P2	P1	2022-02-28	(O1,S1,P1,P2)	Y
P3	S2	2022-03-01	(S2,P3)	N
P4	S3	2022-03-01	(O3,S4,P4)	Y
P4	O3	2022-03-04	(O3,S4,P4)	Y

How do I write the column calculation for member_set in Orders and Products table, and Map_IND column for products table?

The original data has 100 thousands data, I tried lookup function, and failed T.T

Thanks!

[sarahzhou]

And, Products_Mapped be

Prod_ID	HasMembers	ProdEP_DT	Member_Set	Map_IND
P1	S1	2022-01-30	(O1,S1,P1,P2)	Y
P1	O1	2022-01-30	(O1,S1,P1,P2)	Y
P1	P2	2022-01-30	(O1,S1,P1,P2)	Y
P2	S1	2022-02-28	(O1,S1,P1,P2)	Y
P2	O1	2022-02-28	(O1,S1,P1,P2)	Y
P2	P1	2022-02-28	(O1,S1,P1,P2)	Y
P3	S2	2022-03-01	(S2,P3)	N
P4	S3	2022-03-04	(O3,S4,P4)	Y
P4	O3	2022-03-04	(O3,S4,P4)	Y

has a typo in second last row. 

Thanks

[PeterClemmensen]

I'm not sure I follow the logic entirely here. 

So instead of me guessing, could you explain the exact logic? For example, how is member_set made up? Is that made up from Order_ID? And what is the logic behind MAP_IND?

[mkeintz]

In addition to explaining the logic you want to apply, as requested by @PeterClemmensen , please provide the initial sample data sets in the form of working data steps.  This will make it far more likely that the responses you get have been tested.