Date:         Fri, 12 Mar 2010 13:59:00 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: SAS SQ pass through to update a table in sybase based on a
              local              table
Comments: To: Frank Albany <bxz01@HEALTH.STATE.NY.US>
In-Reply-To:  <201003121712.o2CBlNrY017153@malibu.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Frank:
Sybase won't understand the "...work.tmp..." path that you are passing thru to the Sybase compiler. Remember that Sybase is executing the query much as if you were submitting it as a shell script in the RDBMS environment.

This disconnect between SAS SQL and an RDBMS doesn't have a general solution as yet, at least one that I've seen work well, but a couple of workarounds might solve the problem. If you have access to the RDBMS server environment, you may be able to create a tmp table. For a relatively small number of values in work.tmp, you may be able to assign them to a macrovariable list.
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Frank Albany
Sent: Friday, March 12, 2010 12:12 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS SQ pass through to update a table in sybase based on a local table

Hello,

I am trying to update a table in sybase based on a table in my work
directory:
proc sql;
   connect to sybase (user= pass=xxxx);
   execute (update log_sheet as a
   set f_date='12/31/2010'
   where a.id in (select b.id from work.tmp asb)
 ) by sybase;
disconnect from sybase;
   quit;

It does not work.  I know you can create a temp table in sybase and access
it.  Does anyone have a better solution?

Thanks in advance for your help.

Frank