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
PROC Star

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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