topic Re: proc sql update in SAS Programming

proc sql update

ali_far — Wed, 01 May 2019 06:02:02 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:17:39 GMT

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

Re: proc sql update

ali_far — Wed, 01 May 2019 08:26:44 GMT

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;

Re: proc sql update

PeterClemmensen — Wed, 01 May 2019 09:10:45 GMT

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

Re: proc sql update

ali_far — Wed, 01 May 2019 09:17:32 GMT

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

Re: proc sql update

PeterClemmensen — Wed, 01 May 2019 10:51:23 GMT

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?

Re: proc sql update

PeterClemmensen — Wed, 01 May 2019 23:05:15 GMT

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?

Re: proc sql update

ali_far — Mon, 06 May 2019 07:13:33 GMT

Hi draycut,

your code works well