TRANWRD to replace numbers with characters

[lbarwick]

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;

[FreelanceReinh]

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

[lbarwick]

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?

[FreelanceReinh · Accepted solution]

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.

[novinosrin]

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

[lbarwick]

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.

[error_prone]

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.