Help using Base SAS procedures

Mapping to multiple fields

Accepted Solution Solved
Reply
Super Contributor
Posts: 259
Accepted Solution

Mapping to multiple fields

Is there a quick simple way to achieve the following:

Table 1 (before)

ObsHeader 2Header 3Header 4
11312
27112
312153

Mapping

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

Table1(after)

ObsHeader 2Header 3Header 4
1AAACAL
2AGAKAB
3ALAOAC

Accepted Solutions
Solution
‎07-03-2014 10:31 AM
Super User
Super User
Posts: 7,942

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,

          (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;

View solution in original post


All Replies
Super User
Posts: 10,018

Re: Mapping to multiple fields

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

Trusted Advisor
Posts: 3,211

Re: Mapping to multiple fields

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

---->-- ja karman --<-----
Contributor
Posts: 30

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

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;

Trusted Advisor
Posts: 1,228

Re: Mapping to multiple fields

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;

Solution
‎07-03-2014 10:31 AM
Super User
Super User
Posts: 7,942

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,

          (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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 280 views
  • 0 likes
  • 6 in conversation