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
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?
proc SQL;
update table1
set B = (select distinct B from table2
where table1.id=id
group by ID
having A=max(A));
quit;
Ok, so B is not the only variable you want to update? What is the logic here? 🙂
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?
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?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.