Logic needed!

[deleted_user]

This is my first SAS assignment so I would appreciate if you couls elaborate further.

[deleted_user]

Can I use a Merge without a BY to get the temporary table ?

[sbb]

You need the BY statement for the MERGE to work properly - this process will combine your conditional variable values based on the BY key.

Share what code you have thus far, for comment/feedback, preferably within a SASLOG output (pasted in your reply), along with a PUT _ALL_; to show how your TRANWRD function usage (in two assignment statements) resolves values.

Scott Barry
SBBWorks, Inc.

[deleted_user]

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!!!

[sbb]

You must execute two assignment statements, each for the data-string you want to translate.

Where is your MERGE code to combine the transposed variables - you will use those variables in your TRANWRD invocations.

Scott Barry
SBBWorks, Inc. Message was edited by: sbb

[deleted_user]

Yes I might have around 10 variables.So can I use a loop of 1=1 to 10.
And I am not sure how to use the merge statement? I tried but then I thought transpose would be enough. Can you please help?

[sbb]

From what I understand, you have two SAS files and one of those files contains the "key variables" along with some variables you want to merge onto the other SAS file. The TRANSPOSE is needed on the "vertical" file only to make the data-value variables horizontal -- you must maintain your BY variables in order to do the MERGE.

Think of this as a type of "mail merge" where you have a template file/document and variable information that must be superimposed onto the template. In this case, you are using TRANWRD in one or more assignment statements. And, yes, you can use a DO loop - my recommendation is to have two ARRAY definitions, one for the "template" data-string value and another with the transposed values you created with the PROC TRANSPOSE invocation. By using ARRAYs, your SAS DATA step program DO/END loop can make use of the DIM(array_name) function instead of having a hardcoded DO statement range.

My suggestion is that you start with a simple example first to get the logic working, then apply the concept to your production data (select a few obs for a couple of BY groups). For this exercise, you can generate a subset file from your production using some candidate observations, creating a permanent file for your testing, and then refine your SAS programming logic using this sample subset files along with your additional observations that need to be transposed as you have demonstrated in your post. After mapping out a processing flow on paper or whatever, then get your code working, and come back to the forum for feedback, if needed, on technique and specific problem/concern.

Scott Barry
SBBWorks, Inc.

[deleted_user]

This is exactly what I am doing.This is just the test data that I am using. And my intention to post is to get the best possible approach from experts like you. My timelines are very stringent so I try to get opinions. Please bear with me.
I did a sort,transpose and then merged the 2 datasets. and then used the tranwrd function and it worked.

Thank you!! Message was edited by: sasnewbee

[Peter_C]

why has someone tasked you to create some program in SAS "on stringent guidlines" when you are new to SAS?
I would recommend that they send you on a proper SAS programming course so that you can be more effective at programming in SAS than in persuading others to do your programming design - because they might decide that they could use anyone to do that and not just you.

[Ksharp]

Hi. I am curiosity that if two dataset have the same ' Customer ',why not be 'THIS EVT IS TRANSACTION CASE FOR JOHN SMITH, ADAM SMITH & BBC COMPANY. '