topic Re: TRANWRD with 2 same arguments. in SAS Programming

TRANWRD with 2 same arguments.

deleted_user — Wed, 21 Jul 2010 18:53:44 GMT

A small issue.
Given below is the extract of my output.
Cust_no Cust_id
1000 2 The customer was JANE SMITH and account provided was 234567 & 678954. A relationship was found between JANE SMITH and GARY JOHNSON.
1000 2 They are both owners on a deposit account 234567 & 678954.

Now the input table has 2 corresponding rows as below where FLd is the value of the attr. So the value of attr1 is fld1. And I am replacing all occurrences of 'attr' in MSg_TXT with 'fld' .
Cust_no Cust_TYP Cust_ID MSG_ID MSG_SEQ_NO MSG_TXT fld1 fld2 fld3 fld4 fld5 fld6 fld7 attr1 attr2 attr3 attr4 attr5 attr6 attr7
123 EVT 2 238 5 They are both owners on a deposit account . 234567 678954 HOUSTON BANKING CENTER, HOUSTON, TX JANE SMITH and GARY JOHNSON JANE SMITH “the customer entered the banking center and requested that the teller explain how to wire funds overseas.” Account# Account# BranchName EVPartiesInvolved PARTY NAME TRMText
123 EVT 2 237 4 The customer was and account provided was . A relationship was found between . 234567 678954 HOUSTON BANKING CENTER, HOUSTON, TX JANE SMITH and GARY JOHNSON JANE SMITH “the customer entered the banking center and requested that the teller explain how to wire funds overseas.” Account# Account# BranchName Parties PARTY NAME TRMText

And this is my code
data MYDATA.e_final;
set mydata.e_join;
array fld_nm_var (*) attr: ;
array fld_txt_var(*) fld:;
do i=1 to dim(fld_nm_var);
fld_txt = fld_txt_var(i);
fld_name = fld_nm_var(i);
tfld_name = "<" || TRIM(LEFT(fld_name)) || "/>" ;
msg_txt =tranwrd(msg_txt,trim(tfld_name),trim(fld_txt));
drop i fld_txt fld_name tfld_name;
end;
run;

Thoiugh the inoput table has fld1 = 234567 and fld2 = 678945, when I run this code, it just uses the first Account# it finds which 234567. But I am expecting both acct numbers. For that matter , any attribute that occurs twice in a observation , should be seen as fld1 & fld2 & fld3 etc.

IS there any way to achieve this?

Thank you!!!

Re: TRANWRD with 2 same arguments.

deleted_user — Wed, 21 Jul 2010 19:42:06 GMT

I know this might not work if more than 2 acct# are present, but this is what I was trying to do with the log. IS there a way to check for end of arrays in SAS?

328 data MYDATA.e_final;
329 set mydata.e_join;
330 array fld_nm_var (*) attr: ;
331 array fld_txt_var(*) fld:;
332 do i=1 to dim(fld_nm_var);
333 fld_name = fld_nm_var(i);
334 tfld_name = "<" || TRIM(LEFT(fld_name)) || "/>" ;
335 if i ^= dim(fld_nm_var) and fld_nm_var(i) = fld_nm_var(i+1) then
336 fld_txt = fld_txt_var(i) || "&" || fld_txt_var(i+1);
337 else
338 fld_txt = fld_txt_var(i);
339
341 msg_txt =tranwrd(msg_txt,trim(tfld_name),trim(fld_txt));
342
343 drop i fld_txt fld_name tfld_name;
344 *PUT '****';
345 end;
346 run;

ERROR: Array subscript out of range at line 335 column 46.
cust_no=100 cust_TYP=EVT cust_ID=3 MSG_ID=234 MSG_SEQ_NO=1
MSG_TXT=THIS IS CASH DEPOSIT CASE FOR JOHN SMITH, ADAM SMITH & BBC COMPANY fld1=CASH DEPOSIT
fld2=JOHN SMITH, ADAM SMITH & BBC COMPANY fld3= fld4= fld5= fld6= fld7= attr1=RSN_CD attr2=PARTY NAME attr3=
attr4= attr5= attr6= attr7= i=7 fld_name= tfld_name=< /> fld_txt=& _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.

Re: TRANWRD with 2 same arguments.

sbb — Wed, 21 Jul 2010 19:55:50 GMT

Whatever happened to the PROC TRANSPOSE recommendation - you can do the same for your "data value" file or have an outer DO/END (for your variable data - with your SET inside) and a "transposed" file for your template mapping values.

I suggest you work on transposing each of your two files - then analyze their content, then develop a process flow (outside of SAS - on paper) to address your information processing requirement, and then start developing the SAS code to address the rqmt.

Honestly, I have to wonder if there is any offline review/thought/discussion or process flow development being done on your own, before you sit down to program your problem in SAS.

So, in summary, as we've discussed, you need to consider PROC TRANSPOSE and MERGE/BY for your two input files, to logically join and manipulate the information before generating a single output row/detail_line or whatever.

Scott Barry
SBBWorks, Inc.

Re: TRANWRD with 2 same arguments.

deleted_user — Wed, 21 Jul 2010 20:09:26 GMT

Yes there is a process flow that I had thought about. When I post a query I also keep testing, just taht if I get help from SAS experts I might finish the testing faster. Now the main part being achieved, these are some small fixes that I have to do depending on the data.
And I am posting only taht part of the code with which I face a problem . SO my entire code does have the 2 transpose and the MErge.
I am trying to concatenate as mentioned in my post above in the last part of the code.

PROC SORT DATA=mydata.e_csa OUT=mydata.e_csa_sort nodupkey;
BY cust_no cust_typ cust_id field_nm field_txt;
RUN ;

proc transpose data=mydata.e_csa_sort out = mydata.e_csa_trans(drop =_name_) prefix = fld ;
by cust_no cust_typ cust_id ;
var field_tx;
run;

proc transpose data=mydata.e_csa_sort out = mydata.e_csa_trans1(drop =_name_) prefix = attr ;
by cust_no cust_typ cust_id ;
var field_nm;
run;
data mydata.e_csa_merge;
merge mydata.e_csa_trans mydata.e_csa_trans1 ;
by cust_no cust_typ cust_id ;
run;

proc sql ;
drop table mydata.e_join;
create table mydata.e_join as
(select * from mydata.e_story_msg_out m
left join mydata.e_csa_merge a
on m.cust_no = a.cust_no and m.cust_typ = a.cust_typ and m.cust_id = a.cust_id
) ;
quit;

data MYDATA.e_final;
set mydata.e_join;
array fld_nm_var (*) attr: ;
array fld_txt_var(*) fld:;
do i=1 to dim(fld_nm_var);
fld_txt = fld_txt_var(i);
fld_name = fld_nm_var(i);
tfld_name = "<" || TRIM(LEFT(fld_name)) || "/>" ;
msg_txt =tranwrd(msg_txt,trim(fld_name),trim(fld_txt));
drop fld_txt fld_name i;
PUT '****';
end;
run; Message was edited by: sasnewbee

Re: TRANWRD with 2 same arguments.

sbb — Wed, 21 Jul 2010 20:36:56 GMT

Not sure the purpose/need for the PROC SQL step, you should have what you need with a MERGE.

Before the TRANWRD assignment can be performed, the "n" number of "like" template values must be consolidated into a single temporary data-string (explore the CAT functions). So, in the final DATA step there must be some preprocessing code within a DO/END, creating the "concanated" data-string, then execute your TRANWRD, once for each of the unique template value that appears in your template MSG_TXT variable.

Scott Barry
SBBWorks, Inc.

Re: TRANWRD with 2 same arguments.

deleted_user — Wed, 21 Jul 2010 20:49:13 GMT

I need the PROC SQL bcuz my MSG_TXT is in the second table.
And I did look at CAT functions and what I am trying ot do is.
But I get an error saying - ERROR: Array subscript out of range at line 368 column 46.

if i ^= dim(fld_nm_var) and fld_nm_var(i) = fld_nm_var(i+1) then
fld_txt = fld_txt_var(i) || "&" || fld_txt_var(i+1);
else
fld_txt = fld_txt_var(i);

I reserached but did not find anything to check for end of array? I thought this might help --i ^= dim(fld_nm_var) , but it didn't.

Re: TRANWRD with 2 same arguments.

sbb — Wed, 21 Jul 2010 20:56:44 GMT

Please follow the other thread back - the MERGE / BY process to combine the "related template" data strings with your "key association" variables. And if you have only one "global" MSG_TXT template, then you need to do two MERGEs, one for the discrete set of MERGE/BY data and then one more to merge on the static template variable(s), as needed.

You really need to get used to PUTLOG "I'm at location #nn" / _ALL_; statements being strategically located throughout your program so you can analyze the flow and how your array subscript variables increment -- the error indicates that you are attempting to associate two arrays of different dimensions - which won't work obviously -- so you logic must be flawed and so it's time to go back to basic process flow analysis, possibly using an oversimplified set of "sample data values", generated either from input data (picking certain data rows), or by "cooking up" some sample data using a DATA step and DATALINES with an INPUT statement using candidate instream data-values that mimic your much larger data population.

The PUTLOG _ALL_ statement is more your friend here to get over the hump with the logic problem -- and you need to learn/know your data to get past the problem.

Scott Barry
SBBWorks, Inc.

Re: TRANWRD with 2 same arguments.

deleted_user — Wed, 21 Jul 2010 21:02:16 GMT

Sorry but I did not get you. Can you please elaborate?
My code works just fine except for this concatenation needed when there are 2/more acct# in the same record.

Re: TRANWRD with 2 same arguments.

sbb — Wed, 21 Jul 2010 22:40:09 GMT

Here's an oversimplified data-sample and process example for your use - it shows only one "multiple field value" condition which you will need to think about how to incorporate additional fields that are similar.

Hope it helps you work through the challenge.

Scott Barry
SBBWorks, Inc.

data sample;
length field value $30;
input key $ field $ value & $;
datalines;
100 Acct 1111111
100 Acct 1111112
100 Name Jane A. Public
200 Acct 2111111
200 Name John Doe
run;
proc sort data=sample;
by key field;
run;
data sample;
set sample;
by key field;
if first.field then ucnt = 1;
else ucnt + 1;
field = cats(field,ucnt);
run;
proc transpose data=sample out=sample_tr (drop=_:) ;
by key;
id field;
var value;
run;
data sample_tr;
set sample_tr;
* SAS-default length $200 for _acct ;
_acct = catx(' ',of acct: );
_name = Name1;
retain msg_template '_Name has Account(s): _Acct';
msg_txt = tranwrd(msg_template,'_Name',trim(_name) );
msg_txt = tranwrd(msg_txt,'_Acct',trim(_acct) );
drop acct: ;
putlog '>DIAG> Resulting merged data: ' / msg_txt //;
run;

Re: TRANWRD with 2 same arguments.

deleted_user — Thu, 22 Jul 2010 18:12:46 GMT

Thanks a lot for your reply!
But this is the error that I am getting for the last part of the code. Do we always need 3 arguments for CATX?
data sample_tr;
76 set sample_tr;
77 * SAS-default length $200 for _acct ;
78 _acct = catx(' ', of acct: );
----
71
ERROR 71-185: The CATX function call does not have enough arguments.

79 _name = Name1;
80 retain msg_template '_Name has Account(s): _Acct';
81 msg_txt = tranwrd(msg_template,'_Name',trim(_name) );
82 msg_txt = tranwrd(msg_txt,'_Acct',trim(_acct) );
83 drop acct: ;
84 putlog '>DIAG> Resulting merged data: ' / msg_txt //;
85 run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
78:9 79:9
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
81:45 82:40

Thanks in advance! Message was edited by: sasnewbee

Re: TRANWRD with 2 same arguments.

sbb — Thu, 22 Jul 2010 18:21:32 GMT

I had no problem running the sample code I submitted - suggest you will want to own the code you end up with, so by all means, create a "test-run" with sample data string values and run it until the results are acceptable with no errors.

Honestly, we've seen your sometimes short-changed log outputs, with not always all the code revealed, so from my perspective, you need to take it from here.

And, yet again, I can't overemphasize how important it is to have self-initiated desk-checking and problem diagnostic skills. Remember this statement is always your friend (as well as the various syntax for SAS comments):

PUTLOG _ALL_;

Good luck.

Scott Barry
SBBWorks, Inc.

Re: TRANWRD with 2 same arguments.

deleted_user — Thu, 22 Jul 2010 18:31:06 GMT

You are right. Your code run prefect whne I run all of it a one time. When I try to run it separately as posted above, the last piece of code gives me the error.

Re: TRANWRD with 2 same arguments.

sbb — Thu, 22 Jul 2010 18:57:14 GMT

Work through your problem - analyze your code, test your code, break up your code, and re-test until you get desirable results -- maybe the DATA step log below can provide a hint about the ERROR symptom and the lack of SAS variables starting with "acct":

1 data _null_;
2 _acct = catx(' ', of acct: );
----
71
ERROR 71-185: The CATX function call does not have enough arguments.

3 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
cpu time 0.01 second

A PUTLOG _ALL_; statement in your DATA step with the assignment statement commented out provides some clues.

Scott Barry
SBBWorks, Inc.