BookmarkSubscribeRSS Feed
sarahzhou
Quartz | Level 8

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!

3 REPLIES 3
sarahzhou
Quartz | Level 8

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
Tourmaline | Level 20

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
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 449 views
  • 0 likes
  • 3 in conversation