Date:         Tue, 6 May 2008 20:18:10 +0300
Reply-To:     Harry <h@CBT.ZOT>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Harry <h@CBT.ZOT>
Subject:      how is join done in sybase proc sql?
Comments: To: sas-l@uga.edu
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

am trying to convert some sql advantage code to sas

the code looks like

I've already created in proc sql the table temp1 and it contains 2
variables, field and field2

Table1 is a sybase table

select a.*, b.value
 into #Temp2
 from #Temp1 a
join table1 b
 on a.field = b.value

easy, or so I thought

proc sql; create table temp2 as
select a.*, b.value
  from Temp1 a
join table1 b
 on a.field = b.value

nope that does not work, so I tried

proc sql; connect to sybase (user=myid pass='mypass' server=daServer);
 create table temp2 as select * from connection to sybase (
  select a.*, b.value
  from WORK.Temp1 a join table1 b
 on a.field = b.value ); disconnect from sybase;
quit;

nope that does not work, so I tried

proc sql; connect to sybase (user=myid pass='mypass' server=daServer);
 create table temp2 as select * from connection to sybase (
  select b.value
  from table1 b
   where b.value in (select field from WORK.Temp1); disconnect from sybase;
quit;

nope that does not work as sybase does not like to see anything from WORK
library

I can of course select the entire value and then outside the connection to
sybase do the join but that takes a huge wasted table when I suspect there
is a simple way to do the join inside the proc sql between the two tables.

help?