Quartz | Level 8

## Mapping to multiple fields

Is there a quick simple way to achieve the following:

Table 1 (before)

11312
27112
312153

Mapping

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

Table1(after)

1AAACAL
2AGAKAB
3ALAOAC
1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

## Re: Mapping to multiple fields

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,

from    HAVE;

quit;

Or:

proc sql;

create table WANT as

select  A.OBS,

from    HAVE A

left join MAPPING B

left join MAPPING C

left join MAPPING D

quit;

5 REPLIES 5
Super User

## Re: Mapping to multiple fields

Hash Table easy.

```data map;
input key val \$;
cards;
1     AA
2     AB
3     AC
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

Barite | Level 11

## Re: Mapping to multiple fields

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

---->-- ja karman --<-----
Obsidian | Level 7

## Re: Mapping to multiple fields

Hi You can also use the below approach,

data map;

input key val \$;

cards;

1     AA

2     AB

3     AC

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;

Ammonite | Level 13

## Re: Mapping to multiple fields

Simple way.

data have;
datalines;
1 3 12
7 11 2
12 15 3
;

proc format;
1  = 'AA'
2  = 'AB'
3  = 'AC'
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;
run;

proc print data=want;
run;

Diamond | Level 26

## Re: Mapping to multiple fields

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,

from    HAVE;

quit;

Or:

proc sql;

create table WANT as

select  A.OBS,

from    HAVE A

left join MAPPING B

left join MAPPING C