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"
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;
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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.