topic Re: Logic needed! in SAS Procedures

Logic needed!

deleted_user — Mon, 12 Jul 2010 18:35:08 GMT

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

Re: Logic needed!

sbb — Mon, 12 Jul 2010 19:53:56 GMT

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

Re: Logic needed!

deleted_user — Mon, 12 Jul 2010 20:27:05 GMT

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?

Re: Logic needed!

SASKiwi — Mon, 12 Jul 2010 21:25:08 GMT

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!

Re: Logic needed!

Ksharp — Tue, 13 Jul 2010 11:18:14 GMT

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. '

Re: Logic needed!

deleted_user — Tue, 13 Jul 2010 18:00:39 GMT

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?

Re: Logic needed!

RickM — Tue, 13 Jul 2010 18:05:47 GMT

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

Re: Logic needed!

deleted_user — Tue, 13 Jul 2010 18:23:46 GMT

New variable is 3000 char.

Re: Logic needed!

sbb — Tue, 13 Jul 2010 18:57:49 GMT

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.

Re: Logic needed!

deleted_user — Tue, 13 Jul 2010 19:44:12 GMT

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

Re: Logic needed!

sbb — Tue, 13 Jul 2010 19:54:54 GMT

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.

Re: Logic needed!

deleted_user — Tue, 13 Jul 2010 20:13:22 GMT

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

Thank you!

Re: Logic needed!

deleted_user — Tue, 13 Jul 2010 20:18:30 GMT

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

Re: Logic needed!

sbb — Tue, 13 Jul 2010 20:34:19 GMT

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.

Re: Logic needed!

deleted_user — Wed, 14 Jul 2010 17:12:01 GMT

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!

Re: Logic needed!

sbb — Wed, 14 Jul 2010 18:45:53 GMT

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.

Re: Logic needed!

deleted_user — Wed, 14 Jul 2010 19:23:40 GMT

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

Re: Logic needed!

deleted_user — Wed, 14 Jul 2010 19:26:24 GMT

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

Re: Logic needed!

sbb — Thu, 15 Jul 2010 14:00:42 GMT

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.

Re: Logic needed!

deleted_user — Thu, 15 Jul 2010 15:00:17 GMT

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