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