Creating a Calculated Item for getting the last response call status

[otepalborida]

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

[ballardw]

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;

[otepalborida]

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

[SteveMarshall]

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.