Hi there
I'm trying to populate multiple rows from an underlying table into a calculated item in SAS CI - Does anyone know if it's possible?
Example: A have a customer A in the customer table with a 1:N relation til a order table. In the order table I have order B, C and D that I would like to collate in one string like this 'B, C, D'.
I want to use the calculated item in export to email, where I can only export one row per customer.
By using the calculated item, the export will contain customer id, customer email and the string 'B, C, D'.
I can find the last order and the first - and I can count them - but I can't find a way to show the whole array.
Any inputs will be appreciated 🙂
Regards/Rikke
Hi Rikke, what component of SAS CI are we talking about - SAS Marketing Automation or SAS RTDM?
Your question can be read in two ways (as if you are talking about MA or RTDM), and seems to make sense in both, but the answers would be quite different.
Hi Dmitry
Sorry - that was an important piece of information missing 🙂 I work in SAS MA.
Regards/Rikke
Hi Rikke,
I'm afraid there is no supported "one-step" solution for what you are looking for.
One possible solution would be:
Another possible solution would be to execute a communication on an Order level, and then use a Post Process to aggregate output records. Looks simpler and cleaner, but will leave the contact history recorded at the Order level, which may or may not be appropriate in your situation.
In theory, there exists another solution - very tricky and obscure. If your database supports a simple list aggregation function (like older Oracle, up until 11g2, had "wm_concat" function), you could try using "SQL_FUNCTIONS=EXTERNAL_APPEND" libname option to "add" a newSAS SQL function alias that corresponds to that database function, with Function Category = AGGREGATE. And then try calling it from SAS MA Calculated Item. It's a very deep and scary stuff and I highly doubt it was ever tested to be used that way. You can try at your own risk and tell us the results though. I'm not affiliated with SAS Technical Support, but in their place, i'd tell that such use case isn't supported. Newer Oracle versions (12c) do not have a simple wm_contact function and have a window function LISTAGG instead. Obviously, SAS external_append functionality cannot be extended to window functions. Not sure about other databases.
Good luck!
Hi Dmitry
Thanks a lot for your reply - I thought it wouldn't be simple 🙂 But I will give it a try.
Have a nice day.
Regards/Rikke
Listen to the Reimagine Marketing podcast
Assess your marketing efforts with a free tool
SAS' Peter Ansbacher shows you how to use the dashboard in SAS Customer Intelligence 360 for better results.
Find more tutorials on the SAS Users YouTube channel.
Listen to the Reimagine Marketing podcast
Assess your marketing efforts with a free tool