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.