topic Creating a Calculated Item for getting the last response call status in SAS Customer Intelligence

Creating a Calculated Item for getting the last response call status

otepalborida — Thu, 19 Dec 2019 11:23:06 GMT

Hi,

Good day!

Seeking for help here for those who have a knowledge in creating calculated items in SAS CI Studio.

So the scenario is, i want to get the last response call status of the client using the calculated items.

I have this table, response_history that stores all the responses of clients in different channel (TLS, SMS). But the thing is, i only need to get the last response call status in terms of TLS channel.

I have a columns like this,

CLIENT_ID | CHANNEL | RESPONSE | DATE

111 TLS CALLED 1/1/2019

222 SMS OK 1/1/2019

111 SMS OK 1/2/2019

So expectation is, i can get the first row instead of row 3.

Hope the description and info are enough, tho you can ask me for more details.

Thank you!

Regards,

Otep

Re: Creating a Calculated Item for getting the last response call status

ballardw — Thu, 19 Dec 2019 16:54:34 GMT

I'm not sure if you are looking for a "Customer Intelligence" specific tool or not. I don't have that module but a generic SAS code approach could be:

proc sort data=have (where=(channel='TLS'))
          out=temp;
   by client_id descending date;
run;

data want;
   set temp;
   by client_id date;
   if first.client_id;
run;

Re: Creating a Calculated Item for getting the last response call status

otepalborida — Thu, 19 Dec 2019 17:08:05 GMT

Hi Ballardw, I'm currently using SAS Customer Intelligence Studio for creating calculated items.

Re: Creating a Calculated Item for getting the last response call status

SteveMarshall — Thu, 19 Dec 2019 17:43:43 GMT

while you can create a calculated field for this along the lines of below against contact history and

Substr(max(CAT(Put(<</Contact & Response History/Contact Date>>,YYMMDD10.),<</Contact & Response History/Marketing Cell Code>>)),11)

It's not advised as it uses SAS functions that are not resolved in the Database and will pull data to the compute tier to resolve and would have negative performance impact.

A better option would be to create a database view that did a similar operation and then add the view to the info map.

Something like Create view MostRecentCH_Cell_Cd as

select ch.Customer_ID, Substr(max(Concat(To_Char(CH.Contact Date),cp.Marketing Cell Code)),11)

from cdm.ci_contact_History CH join cdm.CI_Cell_package cp on cp.cell_package_Sk = ch.cell_package_sk

group by ch.customer_ID

Note your example should just involve a single Transaction table.