Hi community,
While I write the below query in SQL : It update the data in the required column.
update Tab1
set ht = b.height
from Tab1 a join Tab2 b
on a.ID = b.p_ID and ht is null and
b.record_date = (select min(record_date) from Tab2 cc where a.ID = cc.p_ID)
The above code represents : where ever the ht is null (in Tab1) - there we should update minimum record_date value of Height from Tab2.
The same code I'm trying to do in PROC SQL.
The update code in proc sql is quiet different from the above code.
I tried, but I end up with some errors. Could anyone please help to suggest a correct code to resolve this issue.
Thanks in advance!
You need to put the whole code that selects the correct value to use into the right side of the set, and only the one condition that selects the value to be replaced has to stay on the main level:
data tab1;
input id ht;
cards;
1 10
2 .
;
run;
data tab2;
input p_id record_date :yymmdd10. height;
format record_date yymmddd10.;
cards;
1 2018-10-18 10
1 2018-10-20 12
2 2018-10-18 12
2 2018-10-20 13
;
run;
proc sql;
update Tab1 a
set ht = (
select b.height
from tab2 b
where a.ID = b.p_ID and b.record_date = (select min(record_date) from Tab2 c where a.ID = c.p_ID)
)
where ht is null;
quit;
Note the two data steps supplying example/test data. Use such in the future to illustrate your issue. Keep in mind that code is always driven by data, so it is essential to know the data (Maxim 3).
You need to put the whole code that selects the correct value to use into the right side of the set, and only the one condition that selects the value to be replaced has to stay on the main level:
data tab1;
input id ht;
cards;
1 10
2 .
;
run;
data tab2;
input p_id record_date :yymmdd10. height;
format record_date yymmddd10.;
cards;
1 2018-10-18 10
1 2018-10-20 12
2 2018-10-18 12
2 2018-10-20 13
;
run;
proc sql;
update Tab1 a
set ht = (
select b.height
from tab2 b
where a.ID = b.p_ID and b.record_date = (select min(record_date) from Tab2 c where a.ID = c.p_ID)
)
where ht is null;
quit;
Note the two data steps supplying example/test data. Use such in the future to illustrate your issue. Keep in mind that code is always driven by data, so it is essential to know the data (Maxim 3).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.