proc sql update

ali_far — Wed, 01 May 2019 04:59:24 GMT

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

Re: proc sql update

PeterClemmensen — Wed, 01 May 2019 07:19:02 GMT

@ali_far, please do not double post 🙂

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

topic proc sql update in New SAS User

proc sql update

Re: proc sql update