topic LOCF but with conditions in SAS Procedures

LOCF but with conditions

sonicview — Tue, 20 Aug 2019 02:05:28 GMT

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"

Re: LOCF but with conditions

novinosrin — Tue, 20 Aug 2019 01:35:13 GMT

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;

Re: LOCF but with conditions

Astounding — Tue, 20 Aug 2019 01:51:43 GMT

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;