topic Re: TRANWRD to replace numbers with characters in SAS Programming

TRANWRD to replace numbers with characters

lbarwick — Tue, 06 Nov 2018 15:18:52 GMT

I have a column of data with character-formatted numbers, e.g. the value for a record could be "1, 2, 3, 10, 12". These numbers represent a character label, e.g. 1=One, 2=Two, 3=Three, 10=Ten, 12=Twelve. I am trying to use TRANWRD to replace the numbers with the character labels:

char_data=tranwrd(char_data,'1','One');

char_data=tranwrd(char_data,'2','Two');

char_data=tranwrd(char_data,'3','Three');

char_data=tranwrd(char_data,'10','Ten');

char_data=tranwrd(char_data,'12','Twelve');

What I end up with is "One, Two, Three, One0, OneTwo" because I guess SAS is not treating the original 'numbers' as distinct values but rather using the first number and replacing that first (e.g. in the Ten and Twelve examples).

Any way around this?


data have;
length char_data $20.;
input char_data $;
datalines;
1,2,3,10,12
;
run;

data want;
length char_data $50.; format char_data $50.; informat char_data $50.;
set have;

char_data=tranwrd(char_data,'1','One');
char_data=tranwrd(char_data,'2','Two');
char_data=tranwrd(char_data,'3','Three');
char_data=tranwrd(char_data,'10','Ten');
char_data=tranwrd(char_data,'12','Twelve');

run;

Re: TRANWRD to replace numbers with characters

FreelanceReinh — Tue, 06 Nov 2018 15:47:23 GMT

Hi @lbarwick,

You store several numeric values as a (comma-separated) single character value. Why are you surprised that "SAS is not treating the original 'numbers' as distinct values"?

My recommendation: Store the "original 'numbers'" in a numeric variable. It's easier to concatenate the results afterwards if necessary.

Example:

data temp;
infile datalines dsd;
input val @@;
datalines;
1,2,3,10,12
;

data want(drop=val);
length char_data $50;
do until(last);
  set temp end=last;
  char_data=catx(', ',char_data,propcase(put(val,words.)));
end;
run;

Result:

         char_data

One, Two, Three, Ten, Twelve

Re: TRANWRD to replace numbers with characters

lbarwick — Tue, 06 Nov 2018 15:51:32 GMT

What I meant was, the TRANWRD function is not treating value of second argument as distinct. Shouldn't the value enclosed in quotes in second argument be treated as a distinct value?

Re: TRANWRD to replace numbers with characters

FreelanceReinh — Tue, 06 Nov 2018 16:07:35 GMT

The TRANWRD function treats '1' as a (sub)string of length 1, i.e., as a single character. And this character is found and replaced in the string "..., 10, ...". You could replace the longer substrings ('10', '12') first to avoid this.

The function name "TRANWRD" can be misleading because other functions referring to "words" (such as COUNTW or FINDW) use a specific definition of a "word" (involving delimiters). TRANWRD, however, does not use this concept. It is older than COUNTW and FINDW, so the confusion has "historical" reasons.

Re: TRANWRD to replace numbers with characters

novinosrin — Tue, 06 Nov 2018 16:15:42 GMT

classically put . Thank you

Hi @lbarwick Not a solution but the demo(reverse trnwrd) clarifies @FreelanceReinh 's explanation


data have;
length char_data $20.;
input char_data $;
datalines;
1,2,3,10,12
;
run;

data want;
length char_data $50.; format char_data $50.; informat char_data $50.;
set have;
char_data=tranwrd(char_data,'12','Twelve');
char_data=tranwrd(char_data,'10','Ten');
char_data=tranwrd(char_data,'1','One');
char_data=tranwrd(char_data,'2','Two');
char_data=tranwrd(char_data,'3','Three');
/*char_data=tranwrd(char_data,'10','Ten');*/
/*char_data=tranwrd(char_data,'12','Twelve');*/

run;

SAS Output

The SAS System

char_data
One,Two,Three,Ten,Twelve

Re: TRANWRD to replace numbers with characters

error_prone — Tue, 06 Nov 2018 16:17:04 GMT

Reordering the tranwrd-calls could help solving the issue: start with replacing 12, then 11 etc
But the better solution is not having value list in a variable, such design create headaches sooner or even sooner.

Re: TRANWRD to replace numbers with characters

lbarwick — Tue, 06 Nov 2018 16:43:52 GMT

Thanks. Didn't think to put the 1-9 at the end. That seems to have worked. I realize the data are not ideal but in my use case it is the only option I have.