A bit more of a verbose solution but easy to follow and modify.
data have;
infile datalines truncover;
input R_TYP $250.;
datalines;
son,daughter
uncle
aunty
son,uncle
;
data lookup;
infile datalines dsd dsd truncover;
input K_TYP :$100. S_TYP :$100.;
datalines;
son,family
daughter,family
uncle,extended family
aunty,extended family
;
*find number of values per row for array dimensions;
data _null_;
set have end=eof;
retain max_num 0;
n_words=countc(r_typ, ',')+1;
max_num=max(n_words, max_num);
if eof then
call symputx('num_words', n_words);
run;
%put &num_words;
*create format for mapping;
data lookup_fmt;
set lookup;
start=k_typ;
label=s_typ;
type='C';
fmtname='family_fmt';
run;
proc format cntlin=lookup_fmt;
run;
*map codes to new values;
data mapped_summarized;
set have;
array _original(&num_words.) $100.;
array _mapped(&num_words.) $100.;
do i=1 to countc(r_typ, ",")+1;
_original(i)=trim(scan(r_typ, i, ','));
_mapped(i)=put(_original(i), $family_fmt.);
end;
*concatenate output as desired;
r_typ_new=catx(", ", of _mapped(*));
run;
@Anuz wrote:
Hi All,
What is the best way to find and replace values in a field separated by , from values in a mapping table
DATA HAVE;
length R_TYP $250. ;
infile datalines ;
input R_TYP;
datalines;
son,daughter
uncle
aunty
son,uncle
;
run;
DATA TEST_MAPPING;
length K_TYP $100. S_TYP $100.;
infile datalines delimiter=',' dsd truncover;
input K_TYP S_TYP;
datalines;
son,family
daughter,family
uncle,extended family
aunty,extended family
;
run;
DATA WANT;
length R_TYP $250. ;
infile datalines ;
input R_TYP;
datalines;
family,family
extended family
extended family
family,extended family
;
run;
The output in WANT table is what I need as my final output by checking values in the MAPPING table - TEST_MAPPING
... View more
