topic Re: Mapping to multiple fields in SAS Procedures

Mapping to multiple fields

brophymj — Sat, 21 Jun 2014 07:44:18 GMT

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

Re: Mapping to multiple fields

Ksharp — Sat, 21 Jun 2014 13:08:59 GMT

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

Re: Mapping to multiple fields

jakarman — Sat, 21 Jun 2014 14:16:22 GMT

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

Re: Mapping to multiple fields

naveen20jan — Thu, 03 Jul 2014 06:25:40 GMT

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;

Re: Mapping to multiple fields

stat_sas — Thu, 03 Jul 2014 14:25:19 GMT

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;

Re: Mapping to multiple fields

RW9 — Thu, 03 Jul 2014 14:31:38 GMT

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;