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 |
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;
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
Why not use formats. They are designed to do mappings.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.