LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (December 2008, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Tue, 23 Dec 2008 19:28:41 -0500
Reply-To:   "Howard Schreier <hs AT dc-sug DOT org>" <schreier.junk.mail@GMAIL.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   "Howard Schreier <hs AT dc-sug DOT org>" <schreier.junk.mail@GMAIL.COM>
Subject:   Re: Updating problem directly in to oracle database

On Tue, 23 Dec 2008 08:54:26 -0800, raj <raj13232.s@GMAIL.COM> wrote:

>DATA ORA.one; > MODIFY ora.one COMBINED_FINAL; > BY state comp ID; > WHERE state=&state AND comp="&comp" AND ID=&id; > IF _IORC_ = %SYSRC(_SOK) THEN REPLACE; >RUN; > >i am able to update if i copy the oracle table in sas dataset and then >update > >but when i update directly to the oracle table from sas its giving >errors as oracle table as higher columns than what i am updating. >ID=. S_ID=. TM_ID=. comp= state=. DWC_ID=. val=. val1=. >COUNT=. MEAN=. STD_DEVIATION=. SCORE=. P_VA=. > >OR >if i use sql the issue is,if data is not matching then its upating the >values with missing. >PROC SQL; >UPDATE ora.one c >SET P_value =(SELECT s.P_value FROM COMBINED_FINAL s where c.ID= >s.id and c.comp=s.compand c.state=s.state) , > Z_SCORE=(select s.Z_SCORE from COMBINED_FINAL s where c.ID= s.id >and c.comp=s.compand c.state=s.state) >; >QUIT; > >how to update is their any solution to update and retaining the values >if it is not present by using SAS or Proc sql > >Thanks, >Raj

SQL is updating all rows of ORA.ONE because there is no WHERE clause in the UPDATE statement to restrict it. Code something like this (untested) after the SET clause:

where catx( '|' , id , comp , state ) in (select catx( '|' , id , comp , state ) from COMBINED_FINAL)

That should work in SAS. Getting the process to run on the Oracle side is going to be tricky at best.


Back to: Top of message | Previous page | Main SAS-L page