Hi Community,
Here I have an update query which I used often in same method on different tasks.
I need a macro to utilize the update query in further days.
data tab1;
input ID height waist;
cards;
1 . .
2 . .
3 . .
;
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
3 2017-09-11 15
;
run;
data tab3;
input p_id record_date :yymmdd10. Waist;
format record_date yymmddd10.;
cards;
1 2017-10-18 50
1 2016-10-20 45
2 2018-10-18 30
2 2018-10-20 28
3 2017-09-11 60
;
run;
In the Tab1 I need to update the Min date value of Height,Waist from Tab2 and Tab3.
The given update query will resolve the task. I rerun it on daily basis for different table IDs'.
proc sql;
update Tab1 a
set height = (
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 height is null;
quit;
Proc sql;
update Tab1 a
set waist = (
select b.Waist
from tab3 b
where a.ID = b.p_ID and b.record_date = (select min(record_date) from Tab3 c where a.ID = c.p_ID)
)
where waist is null;
quit;
I don't know how to implement a macro in the update code.
Please suggest me a macro code for the update query.
Thanks in Advance!
Should be something like this:
%macro myUpdate(dsn, var, from);
proc sql;
update &dsn. as a
set &var. = (
select &var.
from &from.
where a.ID = p_ID and record_date = (select min(record_date) from &from. where a.ID = p_ID)
)
where &var. is null;
quit;
%mend myUpdate;
%myUpdate(Tab1, Height, Tab2);
%myUpdate(Tab1, Waist, Tab3);
(untested)
Should be something like this:
%macro myUpdate(dsn, var, from);
proc sql;
update &dsn. as a
set &var. = (
select &var.
from &from.
where a.ID = p_ID and record_date = (select min(record_date) from &from. where a.ID = p_ID)
)
where &var. is null;
quit;
%mend myUpdate;
%myUpdate(Tab1, Height, Tab2);
%myUpdate(Tab1, Waist, Tab3);
(untested)
You are Awesome Buddy!
What you did for me was kind of a big deal. Thank you so much @PGStats.
Dear @PGStats
Thank you for taking your valuable time to throughly review my post.
The code you suggested works well. I have a little complication in the code when it comes to different condition.
I failed on the SET statement that given below. While I try, Its really quiet different to write a code.
UPDATE Tab1
SET fvNPDR_lt = Is_RD_NPDR_Left,
fvNPDR_rt = Is_RD_NPDR_Right,
fvPDR_Lt = Is_RD_PDR_Left,
fvPDR_rt = Is_RD_PDR_Right
FROM Tab1 a join eye_diagnosis b
on a.mno = b.patient_id and fvNPDR_lt is null and fvNPDR_rt is null and fvPDR_Lt is null and fvPDR_rt is null and
b.Advice_Date = (select max(Advice_Date) from eye_diagnosis cc where cc.patient_id = a.mno)
update Tab1
set fvfbs = b.Test_Result_Value
FROM Tab1 a JOIN (select * from test_results union select * from test_results_archive) b
ON a.mno = b.patient_id and fvfbs is null and
b.record_date = (select max(record_date) from (select * from test_results where param_id = 'fbs' union select * from test_results_archive where param_id = 'fbs') cc where cc.patient_id = a.mno) and
param_id = 'fbs'
I need some guidance to solve the given code which using UNION, NULL statements by utilizing MACRO.
Thanks in advance!
You cannot update the tables involved in a join with SAS/SQL. To update the records where all 4 fv* variables are missing with the values from table eye_diagnosis corresponding to the latest advice_date, you would need something like this:
UPDATE Tab1 as a
SET
fvNPDR_lt = (select Is_RD_NPDR_Left from eye_diagnosis where patient_id=a.mno having advice_date=max(advice_date)),
fvNPDR_rt = (select Is_RD_NPDR_Right from eye_diagnosis where patient_id=a.mno having advice_date=max(advice_date)),
fvPDR_Lt = (select Is_RD_PDR_Left from eye_diagnosis where patient_id=a.mno having advice_date=max(advice_date)),
fvPDR_rt = (select Is_RD_PDR_Right from eye_diagnosis where patient_id=a.mno having advice_date=max(advice_date))
where fvNPDR_lt is null and fvNPDR_rt is null and fvPDR_Lt is null and fvPDR_rt is null;
The steps of macro development:
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.