Prev: update table
Next: SAS/Connect not able to logoff mainframe under RACF
From: jaheuk on 4 Nov 2009 07:48
this is a first small test that works,
however a new record is not added to the table
data test;
input a b ;
cards;
1 2
3 4
5 6
10 58
15 20
;
run;
data test1;
input a b ;
cards;
1 22
12 4
115 6
15 30
;
RUN;

proc sql;
update test k
set b = (select l.b from test1 l
where k.a=l.a )
where a in(select a from test1 )
;
quit;

regards,
Herman

===============================================================================================
On Nov 4, 10:14 am, peesari.mah...(a)GMAIL.COM ("SUBSCRIBE SAS-L Joe H.
Smith") wrote:
> hi all,
>
> i have two tables i want to update master table ,i am wriing the code in proc it
> gives me following error.
>
> data test;
> input a b z;
> cards;
> 1 2 100
> 3 4 35
> 5 6 .
> 15 58 68
> 15 20 65
> ;
>
> data test1;
> input a b c;
> cards;
> 1 22 1
> 12 4 2
> 115 6 3
> 15 20 4
> ;
> proc sql UNDO_POLICY=NONE;
> update test t1
> set z=(select c from test1 as t1 ,test as t2 where t1.a=t2.a)
> where (select t2.a from test1 t2 ,test t1 where t1.a=t2.a and t1.b=t2..b);
> quit;
>
> error:
> WARNING: A value expression of the SET clause references the data set being
> updated.
> WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an error
> is detected when processing this UPDATE statement, that
>          error will not cause the entire statement to fail.
> ERROR: Subquery evaluated to more than one row.
>
> o/p i need is
> a b z
> 1 2 100
> 3 4 35
> 5 6 .
> 15 58 68
> 15 20 4
>
> thanks all

 | 
Pages: 1
Prev: update table
Next: SAS/Connect not able to logoff mainframe under RACF