Search and replace within a string of variables

[mariko5797]

I want AVISIT to be proper case while maintaining all caps for my abbreviations.

My attempt is yielding inconsistent outcomes...

data have;
 length visit $22.;
 input visit $20.;
 cards;
 INITIAL SCREENING
 MOCK CHMI DAY 1
 MOCK CHMI DAY 21
 CHMI1 DAY 1
 CHMI1 DAY 2
 CHMI2 DAY 1
 CHMI3 DAY 1
 CHMI3 DAY 8
 CHMI3 DAY 12
 ;
run;

data want;
 length visit $22.;
 input visit $20.;
 cards;
 Initial Screening
 Mock CHMI Day 1
 MOCK CHMI Day 21
 CHMI 1 Day 1
 CHMI 1 Day 2
 CHMI 2 Day 1
 CHMI 3 Day 1
 CHMI 3 Day 8
 CHMI 3 Day 12
 ;
run;

My attempt:

data test;
 set have;
 avisit_ = propcase(visit);
 avisit = translate(avisit_, 'Chmi', 'CHMI ');
run;

Outcome:

I initially attempted this code with more variables within a dataset and ended up with an empty column and this in the log file (the 'Note' repeated). VISIT format and informat are both characters, I don't quite understand why it is converting to numeric. The 'Note: Invalid numeric data...' is also not consistently written with each run, but I still get the conversion notes.

581  data test0;
582   set _work00_;
583      avisit = propcase(visit);
584      avisit = translate(avisit, 'Chmi', 'CHMI');
585      avisitn = visitnum;
586      *if avisit = 'Max Severity Post Baseline' then avisitn = 999;
587      ***;
588      atptn = scan(avisit, -1);
589      if find(avisit, 'Unscheduled') > 0 then atptn = scan(avisitn, 2);
590      if find(avisit, 'Screening') > 0 then atptn = 0.1;
591      if find(vstpt, 'POST MOCK') > 0 then atptn = 1.1;
592      if find(avisit, '28 Days After') > 0 then atptn = 28;
593      atpt = 'Day '||strip(atptn);
594
595      keep usubjid visit visitnum avisit avisitn vstpt atptn atpt;
596  run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      583:14   584:14
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      584:24   588:18   589:13   589:58   590:13   590:51   591:50   592:13   592:55
NOTE: Invalid numeric data, 'Initial Screening' , at line 583 column 14.
NOTE: Invalid numeric data, 'Mock Chmi Day 1' , at line 583 column 14.

Does anyone have any ideas? Thank you in advance!

[yabwon]

data test;
 set have;
 avisit_ = propcase(visit);
 avisit = tranwrd(avisit_, 'Chmi', 'CHMI ');
run;

[ballardw · Accepted solution]

You do not want the TRANSLATE function in this sort of assignment. Translate changes character by character in the parameter lists. ALL 'M' become 'm' with your example. Note that the first parameter in Translate is the "to value list" and the second parameter is the "from value list". You wouldn't be the first person to confuse which order you want the parameters.

584      avisit = translate(avisit, 'Chmi', 'CHMI');

You likely want the TRANWRD function which changes a group of characters for an entire different group, and the "from value" is the first parameter and the "to value" is the second parameter.

avisit = tranwrd(avisit,'Chmi','CHMI');

If your "word" is possibly part of another word where you wouldn't want to change it you may need considerably more logic.