Solved

matching two tables

Posted on 2011-09-23
2
256 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

743 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

10 Experts available now in Live!

Get 1:1 Help Now