BookmarkSubscribeRSS Feed
sonicview
Fluorite | Level 6

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"

 

2 REPLIES 2
novinosrin
Tourmaline | Level 20

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
Opal | Level 21

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;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 401 views
  • 3 likes
  • 3 in conversation