Update statement in proc SQL

[Sathish_jammy]

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!

[Kurt_Bremser · Accepted solution]

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).