This is the code I managed till now:
CSA table
Cust_id cust_typ cust_no Field_nm Field_txt
123 EVT 23 PARTY NAME JOHN SMITH, ADAM SMITH & BBC COMPANY
123 EVT 13 PARTY NAME APRIL LI ,BBC COMPANY
123 EVT 3 PARTY NAME JOHN SMITH, ADAM SMITH & BBC COMPANY
123 EVT 3 REASON_CD CASH DEPOSIT
123 EVT 13 REASON_CD CASH DEPOSIT
123 EVT 23 REASON_CD TRX CTRY WIRE
Story_msg_out table:
Cust_id cust_typ cust_no msg_id msg_seq_no msg_txt
123 EVT 23 234 1 THIS IS ABOUT
123 EVT 13 23456 1 THIS IS NOTHING
123 EVT 3 234 1 THIS EVT IS
CASE FOR
libname mydata 'C:\' ;
proc sql ;
drop table mydata.epc_story_tmp;
create table mydata.epc_story_tmp as
(select distinct a.cust_id, a.cust_typ, a.cust_no, a.field_nm, a.field_tx ,m.msg_id, m.msg_seq_no,m.msg_txt from mydata.epc_csa a
left join mydata.epc_story_msg_out m
on m.cust_id = a.cust_id and m.cust_typ = a.cust_typ and m.cust_no = a.cust_no
) ;
quit;
PROC SORT DATA=mydata.epc_story_tmp OUT=mydata.epc_story_sort ;
BY cust_id cust_typ cust_no msg_id msg_seq_no msg_txt;
RUN ;
proc transpose data=mydata.epc_story_sort out = mydata.tmp ;
by cust_id cust_typ cust_no msg_id msg_seq_no msg_txt;
var field_tx ;
run;
TMP table after transpose looks like this.
Cust_id cust_typ cust_no msg_id msg_seq_no msg_txt
123 EVT 3 234 1 THIS EVT IS
CASE FOR
Formervariable col1 col2
FIELD_TX CASH DEPOSIT JOHN SMITH, ADAM SMITH & BBC COMPANY
123 EVT 13 23456 1 THIS IS NOTHING FIELD_TX CASH DEPOSIT APRIL LI ,BBC COMPANY
123 EVT 23 234 1 THIS IS ABOUT
FIELD_TX TRX CTRY WIRE JOHN SMITH, ADAM SMITH & BBC COMPANY
I am using a tranwrd function on this tmp table. But how will tranwrd serach for 2 difft strings(party and reason_Cd) in the same text? IS there any other way to achieve this? I did not put the log since all queries run fine.
data MYDATA.tmp;
set mydata.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;
Thank you!!!
