These are very helpful, thank you for the references. A couple of follow-up questions: 1) Would you say K-means clustering works best with customer segmentation? We have many features so just want to see which works best - K-means, SOM, etc. and 2) Is the Silhouette coefficient the best metric to evaluate any clustering algorithm or specifically K-means? Thanks for the help! ... View more

Yes, this is my first thought. But I would like to return an empty table instead. if &_sampsize. = 0 then do; data CAD.RAND_DEST; set CAD.SOURCE; run; end; Would this work? Each output table (random sample) will be appended to main table. ... View more

A couple of more notes. To be eligible for: category A, flag1 = 1 or flag2 = 1 category B, flag2 = 1 category C, flag1 = 1 and flag3 = 1 category D, flag2 = 1 category E, flag3 = 1 ... View more

@Reeza @Astounding    I drew a couple of quick visuals of the workflow:   Clarifications:   If they did NOT open application on day 2 (after being assigned to category A) AND they are not eligible to category B, they skip a day and are moved to category C on day 3. Category D and E have 'capacities'; ex: if 25k on day 3 did not open an application (after being assigned to category C) AND are eligible for category D, only 5k can be assigned to category D. They are dropped for day 4 and are either, 1) moved to next week's batch or 2) moved to the next day batch. I would like to implement both as we have no decision on this yet. Similarly for category E. 10K on day 2 are customer the next day's batch who are eligible for category A. There are fresh batches everyday. 9k in category B are customers who did NOT open an application (after being assigned to category A) AND are eligible for category B. Customers who open an application at any point are excluded from this flow for 30 days. If after being assigned category E (last category) and customer has not opened an application, they are excluded from this flow for 30 days. ... View more

Everyday we generate, for X million customers, a recommended product. The table looks like this. The value column is used when creating daily batches - sort descending by product and by value and take highest 10k batch etc.; but at the moment, that might not be applicable as we want to take a completely random batch regardless of value. The flags will be used as filters to assign categories; ex: if for customer 1, flag1 = 1 and flag2 = 1, they can be assigned CATEGORY A, and so on. We save a 60 day history of this table. id product value flag1 flag2 flag3 run_date 1 A 22.52 1 0 1 18.10.2022 2 B 13.53 1 1 0 18.10.2022 3 A 55.21 1 1 1 18.10.2022   Based on our recommendations, a customer can choose to open an application for that product. To deliver or communicate the product to the customer, we assign customers into categories. These categories ensure the product is delivered to the customer in different ways; ex, over mobile or through email, etc.    Also, everyday a new table is generated for open applications. From this table, we can know if a customer 'reacted' to the product we recommended and was assigned to a particular category, by opening an application. We save a 5 year history of this table. id product application_date 1 A 26.10.2022 3 B 27.10.2022   A couple of notes: We have to generate a new 10k batch everyday, and those 10k are always assigned CATEGORY A unless they are disqualified (from the flags). If they are disqualified, they are pushed into the next day, and can be assigned to CATEGORY B if they qualify.  If you open an application the next day (after being assigned a category the previous day), it's considered a 'success' and you're out. You can't be assigned a category for 30 days, even if we recommend you a different product during that time. CATEGORY D and CATEGORY E have daily capacities. If we have a potential of 25k customers to be assigned to either category but have a daily capacity of 5k for each, we take the 5k customers with the highest values and assign those. The rest of 20k are pushed to the next day's batch. ... View more

Categories are assigned per day basis. Day 1 only has CATEGORY A, day 2 has A and B, etc. 5 days pass, and the flow resets. Month is 30 days; when a customer is assigned a category, they're excluded from all other categories for 30 days. After that, he can again be assigned to any category. If a person is already assigned a category means he opened an application. What happens after that is irrelevant for 30 days. If they open another application during the 30 day period, we just ignore. If enough time passes, a customer can be assigned a different category. Even if their recommended product (see below explanation for opening application) is changed 5 days after being assigned to a category, we ignore. 30 days pass, and we can include them in a new batch   To clarify on what opening application is; every customer gets a recommended product. If we recommend product A, and that customer starts an application for it the next day, he is assigned to a category. Categories are basically baskets; each customer has to satisfy a few requirements before entering that basket.   You must open an application to get assigned a category. If you don't qualify for that category (or categories) on a particular day, you're removed from that day and pushed to next day (maybe you'll qualify for the next available category). They could well not qualify for any basket, and these customers are completely ignored. It's still not 100% clear even me but hopefully I clarified at least half of it. If not macros, how do you think this can be approached?  ... View more

Would something like this work, @Patrick ?   proc append base=TD.TERADATA_TABLE (multiload=yes upsert=yes) data=WORK.SAS_TABLE; run; ... View more

Thanks for this. Only issue is having to write the SAS table in Teradata every time, which can sometimes be very slow for me. Is there a lesser optimized version that updates and/or inserts using SAS syntax?   EDIT: for the MERGE INTO syntax, would it look like this inside the execute command? Assume T is the table in Teradata to be updated/inserted into and S the table from SAS (temp) pushed to Teradata.     merge into T using (select id, product, updated_at from S) as source (a, b, c) on T.id = a when matched then   update set product = b, updated_at = c when not matched then   insert (a, b, c)     This is will update all ids regardless if product doesn't change, just 'force' update all records then insert new instances?  ... View more

Thank you for the reply.   For the second point, can you please direct me to a reference or show me an example? Can't seem to get exactly what I want.    For the third point, would something like this work? This will create the query_date macro. Not sure how I would then convert that into the format I need, which is 2022-01-01, so yyyy-mm-dd.    data _null_;     call symputx('query_date', intnx('year', today(), 0)); run;       ... View more