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
sam2929Asked:
Who is Participating?
 
sanofi-aventisConnect With a Mentor Commented:
I thought I would take a crack at this. I think this might work.

Sincerely,
T-Bone
PROC SQL;

	CREATE TABLE FINAL AS 
	SELECT DISTINCT
		   A1.aa_id as aa_id,
	       A1.lang,
		   CASE WHEN A1.lang = 'E'
		        THEN A1.englishdesc
				ELSE '' END as englishdesc,
		   CASE WHEN A1.lang = 'O'
		        THEN A1.otherdesc
				ELSE '' END as otherdesc,
		   BB.level1_id as level1_id,
		   CASE WHEN A2.lang = 'E'
		        THEN A2.englishdesc
				ELSE '' END as englishlevel1desc,
		   CASE WHEN A2.lang = 'O'
		        THEN A2.otherdesc
				ELSE '' END as otherlevel1desc
	  FROM AA AS A1,
	       AA as A2,
		   BB AS BB
	 WHERE A1.aa_id = BB.bb_id
	   AND A2.aa_id = BB.level1_id
	   AND A1.lang  = A2.lang;

QUIT;

Open in new window

0
 
theartfuldazzlerCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.