SQL is very good for your task.
Here is an alternative in DATA Step.
Q: Would it be more physiologically appropriate to compute an interpolated weight from the weights bounding week 36 if week 36 is not present ?
Example:
code - sample data generator
data have;
call streaminit(1234);
do patid = 1 to 375;
length age_wks 8;
wt_grams = rand('integer', 2250, 7500);
do age_wks = 1 to 50;
* average wt gain from https://www.healthline.com/health/baby/baby-weight-gain;
wt_grams + ifn(age_wks<=16,rand('integer',160,180),
ifn(age_wks<=26,rand('integer',113,150),
ifn(age_wks<=52,rand('integer',57-113),
.)));
* skip data 45% of the time;
if rand('uniform') < 0.45 then continue;
* skip week 36 every 7th patient;
if mod(patid,7) = 0 and age_wks = 36 then continue;
* skip week 35 every 14th patient;
if mod(patid,14) = 0 and age_wks = 35 then continue;
if patid=20 and age_wks <= 40 then continue;
output;
end;
end;
run;
code - select data from last record approaching week 36, overridden by first record after week 36 if that is closer.
* create view with new categorical variable used in subsequent BY processing;
data have_v / view=have_v;
set have;
length wk_zone $6;
wk_zone = ifc(age_wks<=36,'<=36','>36');
run;
* process each patients data, retaining the closest to week 36 information;
data want_36;
set have_v;
by patid wk_zone;
retain wk36_weight wk36_age;
if first.patid then call missing(wk36_weight, wk36_age);
if last.wk_zone and wk_zone = '<=36' then do;
wk36_weight = wt_grams;
wk36_age = age_wks;
end;
if first.wk_zone and wk_zone = '>36' then do;
if missing(wk36_age) or age_wks-36 < 36-wk36_age then do;
wk36_weight = wt_grams;
wk36_age = age_wks;
end;
end;
if last.patid;
keep patid wk36:;
run;
Results
... View more