asked on

matching two tables

I have a table

AA

aa_id lang englishdesc otherdesc
101 O BBBB
101 E hello

102 O KKK
102 E OTHER

103 O HHHH
103 E TEST

BB

bb_ID level1_id
101 102
102 102
103 101

I WANT to join aa with bb based upon aa_id and bb_id and want to have final level1_id get englishdesc and other desc
based upon leve11_id

so final results should look like AA will be master table

final table

aa_id lang englishdesc otherdesc level1_id englishlevel1desc otherlevel1desc
101 O BBBB 102 kkk
101 E hello 102 other

102 O KKK 102 kkkk other
102 E OTHER 102

103 O HHHH 101 BBB
103 E TEST 101 hello

so leve1_id will match with aa_id to get englishlevel1desc and otherleve1desc

theartfuldazzler

Hi:

data AA;
infile cards truncover dlm='|' dsd;
informat aa_id 3.   lang $1.    englishdesc $20. otherdesc $20.;
input aa_id    lang    englishdesc  otherdesc;
cards;
101|O||BBBB
101|E|hello|
102|O||KKK
102|E|OTHER|
103|O||HHHH
103|E|TEST|
;
RUN;


data BB;
infile cards truncover dlm='|' dsd;
informat bb_ID 3. level1_id 3. ;
input bb_ID  level1_id  ;
cards;
101|102
102|102
103|101
;
RUN;

PROC SQL;
  create table NewTable1 as
  select * from work.AA as aa
left join work.BB as bb
on aa.aa_id = bb.bb_id;

 create table NewTable2 as
   select New1.*, aa.englishDesc as  englishlevel1desc, aa.OtherDesc as otherlevel1desc
   from work.NewTable1 as New1
   left join
   work.aa as aa
   on New1.level1_id = AA.aa_id and New1.lang = AA.lang
   order by New1.aa_id, New1.lang DESC;
quit;

Open in new window

ASKER CERTIFIED SOLUTION

sanofi-aventis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial