How to implement a macro in update query of Proc SQL

[Sathish_jammy]

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!

[PGStats · Accepted solution]

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) 

[Sathish_jammy]

You are Awesome Buddy!

What you did for me was kind of a big deal. Thank you so much @PGStats.

[Sathish_jammy]

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!

[PGStats]

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;

[Kurt_Bremser]

The steps of macro development:

1. get working SAS code
   -> you have done that already
2. identify the parts that need to be made dynamic
   -> compare your different steps; in your case, it will be dataset and variable names
3. replace those with macro variables, set the macro variables with %let, and test the code again
4. wrap the code into a macro definition, insert the macro variables as parameters, and remove the %let's
5. test the macro with the values set as parameters; use options mlogic mprint symbolgen;