BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brophymj
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
Ksharp
Super User

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
Barite | Level 11

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

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


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
Ammonite | Level 13

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
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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