LOCF but with conditions

[sonicview]

Hi Experts,

I got stuck at one step in my program. I have two datasets one have the subject number and its weight measurement on various dates.

Another dataset has the subject number and exposure date. 

I have to create a dataset which gives me the weight of a subject on the exposure date, if weight is not taken on the same exposure date and time then have to take the last weight taken for that subject before the exposure date and time.

Below are two datasets...

data weight;
length subjid $8 vsdtc $16 weight 8;
input subjid vsdtc weight;
datalines;
1001 2018-03-09T13:30 10
1001 2018-05-02T10:00 12
1001 2018-08-09T16:00 14
1002 2018-01-01T12:00 6
1002 2018-04-01T13:00 8
1002 2018-05-01T14:00 10
1002 2018-06-01T12:00 11
;
run;

data ex;
length subjid $8 exdtc $16;
input subjid exdtc;
datalines;
1001 2018-04-09T13:30
1001 2018-05-01T10:00
1001 2018-08-10T16:00
1001 2018-08-10T16:00
1002 2018-01-01T12:00
1002 2018-04-01T12:00
1002 2018-06-03
;
run;

I need final dataset as

subjid  exdtc                            weight 

1001   2018-04-09T13:30       10
1001   2018-05-01T10:00       10
1001   2018-08-10T16:00       14

1001   2018-08-10T16:00       14
1002   2018-01-01T12:00        6
1002   2018-04-01T12:00        6
1002   2018-06-03                   11

Your help is appreciated. 

"Thank you"

[novinosrin]

Just one SQL is needed

data weight;
length subjid $8 vsdtc $16 weight 8;
input subjid vsdtc weight;
datalines;
1001 2018-03-09T13:30 10
1001 2018-05-02T10:00 12
1001 2018-08-09T16:00 14
1002 2018-01-01T12:00 6
1002 2018-04-01T13:00 8
1002 2018-05-01T14:00 10
1002 2018-06-01T12:00 11
;
run;

 

data ex;
length subjid $8 exdtc $16;
input subjid exdtc;
datalines;
1001 2018-04-09T13:30
1001 2018-05-01T10:00
1001 2018-08-10T16:00
1002 2018-01-01T12:00
1002 2018-04-01T12:00
1002 2018-06-03
;
run;

proc sql;
create table final as 
select a.*,Weight
from ex as a, weight as b 
where a.subjid=b.subjid	and vsdtc<=exdtc
group by a.subjid, exdtc
having max( b.vsdtc)= b.vsdtc
order by a.subjid, exdtc, vsdtc;
quit;

[Astounding]

It wouldn't shock me if a DATA step turned out to be considerably faster than SQL.  Assuming your data sets are sorted:

data want;
set weight (in=inw) ex (in=inx);
by subjid vsdtc;
retain latest_weight;
drop weight;
if first.subjid then latest_weight = .;
if inw then latest_weight = weight;
if inx;
run;