Mapping to multiple fields

[brophymj]

Is there a quick simple way to achieve the following:

Table 1 (before)

Obs	Header 2	Header 3	Header 4
1	1	3	12
2	7	11	2
3	12	15	3

Mapping

Header 1	Header 2
1	AA
2	AB
3	AC
4	AD
5	AE
6	AF
7	AG
8	AH
9	AI
10	AJ
11	AK
12	AL
13	AM
14	AN
15	AO

Table1(after)

Obs	Header 2	Header 3	Header 4
1	AA	AC	AL
2	AG	AK	AB
3	AL	AO	AC

[Ksharp]

Hash Table easy.

data map;
input key val $;
cards;
1     AA
2     AB
3     AC
4     AD
5     AE
6     AF
7     AG
8     AH
9     AI
10     AJ
11     AK
12     AL
13     AM
14     AN
15     AO
;
run;
data have;
input obs var1 var2 var3 ;
cards;
1     1     3     12
2     7     11     2
3     12     15     3
;
run;
data want(drop=var: val i key);
if _n_ eq 1 then do;
 if 0 then set map;
 declare hash ha(dataset:'map');
  ha.definekey('key');
  ha.definedata('val');
  ha.definedone();
end;
 set have;
 array _v{3} $  _var1 _var2 _var3;
 array v{3} var1 var2 var3;
 do i=1 to dim(v);
  key=v{i};
  if ha.find()=0 then _v{i}=val;
 end;
run;

Xia Keshan

[jakarman]

Why not use formats. They are designed to do mappings.

[naveen20jan]

Hi You can also use the below approach,

data map;

input key val $;

cards;

1     AA

2     AB

3     AC

4     AD

5     AE

6     AF

7     AG

8     AH

9     AI

10     AJ

11     AK

12     AL

13     AM

14     AN

15     AO

;

run;

data have;

input obs var1 var2 var3 ;

cards;

1     1     3     12

2     7     11     2

3     12     15     3

;

run;

data fl ;

set map  end=eof;

   start=key;

   label=val;

   fmtname="fl";

            OUTPUT;

      if eof THEN DO;

            start=.;label='none';hlo='O';OUTPUT;

      end;

      run;

proc format lib=work cntlin=fl;

run;

data want ;

set have ;

format var1 var2 var3 fl. ;

run;

[stat_sas]

Simple way.

data have;
input header2 header3 header4;
datalines;
1 3 12
7 11 2
12 15 3
;

proc format;
value header
1  = 'AA'
2  = 'AB'
3  = 'AC'
4  = 'AD'
5  = 'AE'
6  = 'AF'
7  = 'AG'
8  = 'AH'
9  = 'AI'
10 = 'AJ'
11 = 'AK'
12 = 'AL'
13 = 'AM'
14 = 'AN'
15 = 'AO'
;
run;

data want;
set have;
format header2 header3 header4 header.;
run;

proc print data=want;
run;

[RW9 · Accepted solution]

For my 2p, using formats won't change the underlying data, only how it is displayed.  You could do the following two options in SQL which would change the data:

proc sql;

  create table WANT as

  select  BASE.OBS,

          (select THIS.HEADER2 from MAPPING THIS where THIS.HEADER1=BASE.HEADER2) as HEADER2,

          (select THIS.HEADER2 from MAPPING THIS where THIS.HEADER1=BASE.HEADER3) as HEADER3,

          (select THIS.HEADER2 from MAPPING THIS where THIS.HEADER1=BASE.HEADER4) as HEADER4

  from    HAVE;

quit;

Or:

proc sql;

  create table WANT as

  select  A.OBS,

          B.HEADER2 as HEADER2,

          C.HEADER2 as HEADER3,

          D.HEADER2 as HEADER4

  from    HAVE A

  left join MAPPING B

  on      A.HEADER2=B.HEADER1

  left join MAPPING C

  on      A.HEADER3=B.HEADER1

  left join MAPPING D

  on      A.HEADER4=B.HEADER1;

quit;