Now here is how my data looks like.
STORY_OUT
CUST_ID CUST_TYP MSG_ID MSG_TX
123 EVT 234 THIS EVT IS
CASE FOR
This will find 2 matches in CSA table as below:
CSA
CUST_ID CUST_TYP FIELD_NM FIELD_TX
123 EVT PARTY NAME JOHN SMITH, ADAM SMITH & BBC COMPANY
123 EVT REASON_CD CASH DEPOSIT
I did a left join on the CSA and teh story table which gives me a temp table.This temp table which replicates the story table and an additionals - FIELD_NM, field_txt from the CSA table. This will basically help in using TRANWRD funtion given below.
TMP table
Cust_id cust_typ cust_no field_nm Field_txt msg_id Msg_txt
123 EVT 3 REASON_CD CASH DEPOSIT 234 THIS EVT IS
CASE FOR
123 EVT 3 PARTY NAME JOHN SMITH, ADAM SMITH 234 THIS EVT IS
CASE FOR
I am using the tranwrd function but it updates only one field at a time. But I want the result to be - 'This evt is cash deposit case for john smith,adam smith'. I want to merge the results into one if cust_id,cust_typ and cust_no fields are same.
data MYDATA.epc_story_tmp;
set mydata.epc_story_tmp;
put msg_txt;
msg_txt =tranwrd(msg_txt,trim(field_nm),trim(field_tx));
put msg_txt;
msg_txt = compress(msg_txt,'()');
put _all_;
PUT '****';
run;
Can anyone help please?
Thank!
Thanks!
