I've created a small data set illustrating what I am trying to do with the code that I initially wrote.

In table h10q, I want to update the "4" value that is currently in MQ511M4 to "102" that is pulled from the grids table.

data Grids_new02142;

input sampid $ MX054 MX055;

cards;

0124430040 3 101

0124430040 4 102

0124430040 5 103

0124430040 6 104

0124430040 7 108

;

data H10q_r2;

input sampid $ MQ511M4;

cards;

0124430040 4

;

Thank you for your assistance.

Eduardo

From: Joe Matise [mailto:snoopy369@gmail.com] Sent: Thursday, March 10, 2011 3:33 PM To: Eduardo Galvan Cc: SAS-L@listserv.uga.edu Subject: Re: Proc Sql Update with 2 Tables

Unless I'm missing something you may just have a data issue. But your correlated query isn't perfect, try :

proc sql; update work.H10q_r2 q set mq511m4 = (select g.mx055 from work.Grids_new02142 g where g.sampid = q.sampid and g.mx054 = q.mq511m4) where exists (select 1 from work.Grids_new02142 g where g.sampid = q.sampid and g.mx054 = q.mq511m4) ;

quit;

It's possible you might have an issue with data not being correctly formatted or something else such that it ends up missing in the updated table; you can try debugging that by replacing g.mx055 with a number (say, 1) as I did in the exists subquery and see if anything becomes a 1 (indicating the exists clause passed).

Also, is it possible simply that there are no cases where g.mx054 = q.mq511m4 ? What value is in that variable now (if any)?

-Joe

On Thu, Mar 10, 2011 at 2:10 PM, Eduardo Galvan <egalvan@isr.umich.edu> wrote:

Hi,

I am trying to update a variable (mq511m4) in one table (H10q_r2) with the values of a variable(mx055) in another table (Grids_new02142) using the following code.

proc sql; update work.H10q_r2 q set mq511m4 = (select g.mx055 from work.Grids_new02142 g where g.sampid = q.sampid and g.mx054 = q.mq511m4) where sampid in (select sampid from work.Grids_new02142);

quit;

Unfortunately, after the processing is finished the variable I am trying to update contains only blank data.

Any suggestions on how to fix the code would be appreciated.

Thanks.

Eduardo