proc sql update

[ali_far]

Hi,

I have a simple question:

data table 1;
input id A B C;
cards;
1 2 3 4
2 5 5 5
3 7 6 5
4 9 6 1
5 5 7 2
6 7 8 1
run;

data table 2 ;
input id A B C;
cards;
1 2 3 4
1 4 2 5
1 5 3 12
1 7 34 3
2 5 5 5
2 6 12 0
2 9 3 2
2 11 3 1
3 7 6 5
3 23 3 2
3 12 12 44
4 4 23 33
4 9 6 1
5 5 7 2
6 7 8 1
run;

I want to update  variable B in Table 1 where the id in two tables are the same (first condition) and A is at maximum in table 2.

Because for some id( say 1, 2) in table 2  I have several rows.

So, it will be the updated table:

data table 1;
input id A B C;
cards;
1 2 34 4
2 5 3 5
3 7 3 5
4 9 6 1
5 5 7 2
6 7 8 1
run;

proc SQL;
update table 1 set B= (select B from table 2 where table 1.id=table 2.id  and table 2.A=max); quit;

I know the above code, after the and is not correct!!!

Thanks

[PeterClemmensen]

proc SQL;
   update table1 
   set B = (select distinct B from table2 
               where table1.id=id
               group by ID
               having A=max(A)); 
quit;

[ali_far]

Thanks for your reply. Your code works well for what I wanted but unfortunately I got one mistake in the want data set:

Here is the one I want:

data updated table 1;
input id A B C;
cards;
1 7 34 3
2 11 3 1
3 23 3 2
4 9 6 1
5 5 7 2
6 7 8 1
run;

[PeterClemmensen]

Ok, so B is not the only variable you want to update? What is the logic here? 🙂

[ali_far]

Thanks for your reply.
exactly, other variables might also get updated.

Table 1 gets updated using the same id with table 2 where we the id has the highest value of B. Then other variable relevant to that ID also should be updated.

Hope it is clear!
appreciate your time

[PeterClemmensen]

But that description does not comply with your newly posted want data set? Here, it seems that you want the table1 updated with values from table2 where the ID has the highest value of A and not B?

[PeterClemmensen · Accepted solution]

If you insist on using PROC SQL to do this (and not the usual sort by / last.id logic), then you can achieve your desired result with something like this

data table1;
input id A B C;
cards;
1 2 3 4
2 5 5 5
3 7 6 5
4 9 6 1
5 5 7 2
6 7 8 1
run;

data table2 ;
input id A B C;
cards;
1 2 3 4
1 4 2 5
1 5 3 12
1 7 34 3
2 5 5 5
2 6 12 0
2 9 3 2
2 11 3 1
3 7 6 5
3 23 3 2
3 12 12 44
4 4 23 33
4 9 6 1
5 5 7 2
6 7 8 1
run;

proc sql;
   create table temp as
      select distinct * from 
         (
         select * from table1 
         union all
         select * from table2
         )
      group by ID
      having A=max(A);
quit;

@ali_far did this solve your problem? 

[ali_far]

Hi draycut,

your code works well