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]

@ali_far, please do not double post 🙂 

See my answer in your related thread here and let me know if it works for you