topic Re: Array in calculated item? in SAS Customer Intelligence

Array in calculated item?

RikkeK — Thu, 01 Feb 2018 12:57:46 GMT

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

Re: Array in calculated item?

Dmitry_Alergant — Thu, 01 Feb 2018 14:27:38 GMT

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.

Re: Array in calculated item?

RikkeK — Fri, 02 Feb 2018 09:38:30 GMT

Hi Dmitry

Sorry - that was an important piece of information missing 🙂 I work in SAS MA.

Regards/Rikke

Re: Array in calculated item?

Dmitry_Alergant — Tue, 06 Feb 2018 06:15:34 GMT

Hi Rikke,

I'm afraid there is no supported "one-step" solution for what you are looking for.

One possible solution would be:

Introduce a temporary table in your database (Oracle or whatever it is) on an Order level.
Use an Export or Process node to export data to that table with selected Orders as a first step. \
Have a view created in a database on top of that table that implements the aggregation you need. Oracle has a function "LISTAGG", other databases should have similar aggregate functions.
Infomap this view back to SAS MA, so it can be used for subsequent communication on a Customer level

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!

Re: Array in calculated item?

RikkeK — Mon, 12 Feb 2018 07:38:34 GMT

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