Solved

matching two tables

Posted on 2011-09-23
2
268 Views
Last Modified: 2013-11-16
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
0
Comment
Question by:sam2929
2 Comments
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 36585844
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
 
LVL 2

Accepted Solution

by:
sanofi-aventis earned 500 total points
ID: 36710501
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some very basic things about SQL Server filegroups.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now