Art -

The problem with the technique in the link that you referenced is that it
requires a separate update statement to be coded for every column that is to
be updated.  While the example that I provided only includes two columns to
be updated, the real tables that I am working with include a much larger
number of columns that need to be updated from the second table.  Although I
could write a macro to generate the necessary update statements, executing a
separate update statement for each individual column would be terribly slow.
Any other ideas?

Thanks,
Kevin M.


----- Original Message -----
From: "Arthur Tabachneck" <art297@NETSCAPE.NET>
To: <SAS-L@LISTSERV.UGA.EDU>; "Kevin A. Myers" <kmyers1@CLEARWIRE.NET>
Sent: Tuesday, November 24, 2009 17:15
Subject: Re: Update One Table from Another Table Using SQL


> Kevin,
>
> Doe the thread at the following link provide what you need?:
> http://www.tek-tips.com/viewthread.cfm?qid=1078242&page=1
>
> HTH,
> Art
> -------
> On Tue, 24 Nov 2009 14:45:17 -0600, Kevin Myers <kmyers1@CLEARWIRE.NET>
> wrote:
>
>>Using SQL in SAS 8.2, I would like to update the contents of specific rows
> in one table using rows with matching composite key column values from
> another table.  In other SQL dialects, this could be accomplished using
> code
> similar to the following:
>>
>>/* syntax 1 (Microsoft SQL Server) */
>>update table1
>>set table1.column1=table2.column1, table1.column2=table2.column2
>>from table2 where table1.key1=table2.key1 and table1.key2=table2.key2;
>>
>>/* syntax 2 (Microsoft Access, MySQL) */
>>update table1, table2
>>set table1.column1=table2.column1, table1.column2=table2.column2
>>where table1.key1=table2.key1 and table1.key2=table2.key2;
>>
>>/* syntax 3 */
>>update table1
>>set (table1.column1, table1.column2)=(select column1, column2 from table2
> where table2.key1=table1.key1 and table2.key2.key2=table1.key2);
>>
>>Unfortunately, SAS does not appear to support syntax comparable to any of
> the above options for the UPDATE statement of PROC SQL.  Does anyone have
> any suggestions as to how this can be accomplished using PROC SQL in SAS
> with any code that remains reasonably compact and efficient?
>>
>>Couple of additional notes/restrictions:
>>1. The actual tables may reside in an external database for which the
> specific DBMS is not necessarily known ahead of time.
>>2. It may not be possible to add any other tables or views to the external
> database.
>>
>>Note that I am familiar with comparable update capabilities of the data
> step (using the MODIFY statement, etc.), but a data step based solution is
> undesirable for my specific situation.
>>
>>Thanks,
>>Kevin M.

Hi Kevin,

I don't know , this is an optimal solution or not for your data.

But try this ...hope this helps


data mastertable;
input custid custname $ acctcode masteramt refamt sal;
cards;
1 sam 123 100 200 1000
2 ram 234 200 300 2000
3 ravi 456 400 400 3000
4 shiva 432 900 500 4000
;run;
data ReferenceTable;
input custid custname $ acctcode refamt sal;
cards;
1 sam 123 6000 5000
;run;
PROC SQL;
UPDATE mastertable c
SET  refamt  =(SELECT refamt FROM referencetable s),
sal=(select sal from referencetable s)
where    catx( '|' , custid , custname , acctcode ) in
    (select catx( '|' , custid , custname , acctcode) from
referencetable)
;
QUIT;

Thanks,
shiva

The basic problem is that this in non-standard SQL and SAS has not
implemented.
Shiva's solution will not work when there are multiple rows in the
table of updates.
SAS replies: ERROR: Subquery evaluated to more than one row.

If you need to update multiple ids then you will need to loop through
them one at a time.
Something like this:


data mastertable;
  input custid custname $ acctcode masteramt refamt sal;
cards;
1 sam   123 100 200 1000
2 ram   234 200 300 2000
3 ravi  456 400 400 3000
4 shiva 432 900 500 4000
run;

data referencetable;
  input custid custname $ acctcode refamt sal;
cards;
1 sam 123 6000 5000
2 ram 234 2000 3000
run;

%macro multi_update;
%local idlist i where ;

proc sql noprint;
  select distinct catx('|',custid,custname,acctcode)
    into :idlist separated by '\'
    from referencetable
  ;
%do i=3D1 %to &sqlobs;
  %let where=3Dwhere catx('|',custid,custname,acctcode) =3D"%scan
(&idlist,&i,\)";
  update mastertable c
    set refamt =3D(select refamt from referencetable s &where)
       ,sal    =3D(select sal from referencetable s &where)
    &where
  ;
%end;
quit;

%mend multi_update;

%multi_update;


On Nov 24, 11:15=A0pm, shiva <shiva.said...@gmail.com> wrote:
> Hi Kevin,
>
> I don't know , this is an optimal solution or not for your data.
>
> But try this ...hope this helps
>
> data mastertable;
> input custid custname $ acctcode masteramt refamt sal;
> cards;
> 1 sam 123 100 200 1000
> 2 ram 234 200 300 2000
> 3 ravi 456 400 400 3000
> 4 shiva 432 900 500 4000
> ;run;
> data ReferenceTable;
> input custid custname $ acctcode refamt sal;
> cards;
> 1 sam 123 6000 5000
> ;run;
> PROC SQL;
> UPDATE mastertable c
> SET =A0refamt =A0=3D(SELECT refamt FROM referencetable s),
> sal=3D(select sal from referencetable s)
> where =A0 =A0catx( '|' , custid , custname , acctcode ) in
> =A0 =A0 (select catx( '|' , custid , custname , acctcode) from
> referencetable)
> ;
> QUIT;
>
> Thanks,
> shiva