BookmarkSubscribeRSS Feed
RikkeK
Calcite | Level 5

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

4 REPLIES 4
Dmitry_Alergant
Pyrite | Level 9

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.

-------
Dmitriy Alergant, Tier One Analytics
RikkeK
Calcite | Level 5

Hi Dmitry

 

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

 

Regards/Rikke

Dmitry_Alergant
Pyrite | Level 9

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!

 

 

-------
Dmitriy Alergant, Tier One Analytics
RikkeK
Calcite | Level 5

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

How to improve email deliverability

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.

Discussion stats
  • 4 replies
  • 1128 views
  • 0 likes
  • 2 in conversation