Logic needed!

[deleted_user]

I desperatley need your help. Completely new to SAS. Thsi is one part of what I have to do.

I have 2 tables/SAS datasets.
Customer cust_TYP cust_ID FIELD_NM FIELD_TX
123 EVT 23 PTY NAME JOHN SMITH, ADAM SMITH & BBC COMPANY
123 EVT 13 PTY NAME APRIL LI, BBC COMPANY AND WHOKNOW WHAT
123 EVT 3 PTY NAME JOHN SMITH, ADAM SMITH & BBC COMPANY
123 EVT 3 DC_RSN_DE CASH DEPOSIT
123 EVT 13 DC_RSN_DE CASH DEPOSIT
123 EVT 23 DC_RSN_DE TRANSACTION

Customer cust_typ MSG_id Msg_txt
123 EVT 1 THIS EVT IS CASE FOR


So I have to match these 2 tables/SAS datasets on Customer and then teh final output should be -
THIS EVT IS CASH DEPOSIT CASE FOR JOHN SMITH, ADAM SMITH & BBC COMPANY.

Can you please tell me how to accomplish this?
Any help is appreciated.

Thank you!!

[sbb]

Consider using a DATA step approach with a MERGE and a BY statement (input must be sorted), along with a SAS assignment statement using the TRANWRD function.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

data step programming site:sas.com

tranwrd function site:sas.com

merge by processing site:sas.com

[deleted_user]

I serached and did find the tranwrd function and I was trying to use a variable as the replacement string. But some how it does not work.

data convert;
input @1 address $20. ;
var var1;
var1 = 'John Smith' ;
*** Convert Street, Avenue and
Boulevard to their abbreviations;
Address = tranwrd(address,'Street',var1);
Address = tranwrd(address,'Avenue','Ave.');
Address = tranwrd(address,'Road','Rd.');
datalines;
89 Lazy Brook Road
123 River Rd.
12 Main Street
;

Can you help?

[SASKiwi]

Your code is syntactically incorrect. In the SAS editor the line: VAR VAR1; will be coloured red because it is not valid. Also did you check your SAS log after running this step as it will highlight this statement with an error 180 - this statement is not valid.

Remove the incorrect statement and you will find it works fine. You need to check your SAS log every time you run a program to confirm if it ran without error or not. This is even more important when you are just starting out learning SAS. SAS log messages are usually very good at highlighting errors and problems.

Good luck!

[deleted_user]

Thank you! I did fix that and now trying to use the tranwrd function to replace strings.
But I am facing a problem when I have replace a string in the iddle of a text. It does replace the string but wipes off the text after it in the msg.
So my initial txt is like :
THIS EVT IS FOR .
I have to replace both reason and pty with 'cash deposit' and 'john smith..'. Now it replaces reason but eats up the msg after it.
My o/p shd be :
THIS EVT IS CASH DEPOSIT FOR JOHN SMITH, ADAM SMITH & BBC COMPANY
but my o/p is:
THIS EVT IS CASH DEPOSIT.

Any directions?

[RickM]

Just a thought, what is the length of the new variable? If it is too short it might just be getting truncated.

[deleted_user]

New variable is 3000 char.

[sbb]

You should be able to test the SAS processing behavior outside of your application to verify what's expected of the SAS system, using some "test" data that mimics your application.

Then, if that works, you will want/need to interrogate your code processing, using PUT _ALL_; commands placed in your DATA step at various locations for self-initiated debugging efforts. This diagnosis effort will help increase your confidence that a particular process / function in SAS does in fact work, and then with you being the best individual to debug the situation, you can see about possibly working with a smaller subset of your data and using various techniques like using OBS=nnn or doing a IF _n_ > ???? THEN STOP;

Scott Barry
SBBWorks, Inc.

[deleted_user]

Thanks for ur input. But I did try a few debuggig steps, but it does not work when the string to be replaced is on the middle.I also reduced teh field name from 3000 char to 100 char.


data MYDATA.csa;
set mydata.csa;
put msg_txt;
msg_txt =tranwrd(msg_txt,trim(field_nm),field_tx);
put msg_txt;
msg_txt = compress(msg_txt,'(<)');
put _all_;
PUT '****';
run;


this is my log.
THIS EVT IS CASE FOR
THIS EVT IS
Customer=123 FIELD_NM=REASON_CD FIELD_TX=CASH DEPOSIT MSG_TXT=THIS EVT IS THIS EVT IS

Can anyone please help! Message was edited by: sasnewbee

[sbb]

I ran your code with assigned SAS variables - you need to trim each of your two TRANWRD arguments so that only the non-blank characters are replaced.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Exactly ! thats what I did. I trimmed both my variables field_txt and Field_nm and also reduced their length.

Thank you!

[deleted_user]

I now need to choose the approach. I mentioned that I have MSg in one table and the text that needs to be inserted (field_tx) in another table. Currently I have all these columns in one table. Now if I have to do a join in a data step will it be tedious? Or should I use an additional step before this data step, to add the field_tx data to Msg table, so that I have all in data in one table?

Thanks!!!

[sbb]

If you are still having difficulty with TRANWRD, share the SAS-generated log, not just your "copied" SAS code variables - consider your recent reply had an inconsistent SAS variable name. Honest....the code works, when you have TRIM specified on both arguments #2 and #3 for the TRANWRD assignment statement.

Also -- there's no need to carry more data than absolutely necessary - use a JOIN or DATA step MERGE to get the result needed at the optimal processing point.

Suggest you develop an approach, share the code, and seek feedback from the forum subscribers -- again, you know your data best as we are not familiar with your environment, nor the SAS processing results.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Thank you for following up on this. What I mean in my earlier post was , I did trim 2 variables and it worked out as expected.
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

How can I get both records in this case? I am thinking of creating a 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 if both tehse columns along with MSG_TXT is in the same table. It does not work if I do a left or inner join. If I do an Outer join, the second record of CSA tabel gets added but Story table fields are blank. Any directions or any other suggestions?

Thanks!

[sbb]

I believe that you are working with "vertical" observation oriented data, where you need to access multiple "source table" data-values with multipleTRANWRD searches.

Possibly, look at using PROC TRANSPOSE to create a horizontal perspective for your source data-values, and also a MERGE to associate all related observations using a suitable BY statement, and then you will have your TRANWRD variables to access in your assignment statement.

Scott Barry
SBBWorks, Inc.