Date:         Thu, 27 Nov 2008 13:27:26 +0530
Reply-To:     alex4sas@yahoo.co.in
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Alex S <alex4sas@YAHOO.CO.IN>
Subject:      Help in SQL Join
Content-Type: text/plain; charset=utf-8

Hi All,
   I have an issue with SQL. i am not sure which join i should use to get correct output.
I have two data sets namely T1 ( Fields:Num & Name) and T2 ( Fields:Num & State).In data set t1 has 4 observation and T2 has 3 observation. I want to create a new data set which should have Num, Name and State. The joining key is Num and filter condition is if state="CO" then i should not write this observation.
 
Here is my code:
 
data T1;
input Num 3. Name $4.;
cards;
101Alex
102Raja
103Guru
104Ramu
;
 
data T2;
input Num 3. State $2.;
cards;
101MN
103CO
104NJ
;
 
Proc sql;
Create table output as
Select A.*,B.*
 From T1 A left join T2 B on A.Num=B.Num
 And B.State not in ("CO");
Quit;

Output;
Num Name  State
101 Alex MN
102 Raja
103 Guru
104 Ramu NJ

What i want is
Num Name  State
101 Alex MN
102 Raja
104 Ramu NJ

I guess i am not using the correct join to get my desire output.
Thank you in advance for your help
 
Thanks 'n' Regards 
alex .S.


      Add more friends to your messenger and enjoy! Go to http://messenger.yahoo.com/invite/