Many-to-one association

Altal — Tue, 22 Oct 2013 13:21:02 GMT

Hi All,

Is there a simple way (datastep or sql) to associate multiple observations in a dataset with specific time points to an earlier time point in a one-per-id dataset, as long as the observation falls inbetween time point of interest? an example may explain it better...

I have a dataset with visits dates; and I have treatments with treatment dates. I'd like to associate each trt to the visit earlier as long as the trt was given in the period between the two consecutive visits...

data visits;

input visit $ visitdt mmddyy8.;

format visitdt mmddyy8.;

cards;

1 11/12/12

2 03/02/13

3 04/15/13

4 06/16/13

;

run;

data trt;

input trt $ trtDt mmddyy8.;

format trtDt mmddyy8.;

cards;

A 11/20/12

A 11/29/12

B 12/02/12

A 03/01/13

B 04/15/13

A 04/16/13

B 06/17/13

A 08/15/13

;

run;

I'm interested to this structure...

Visit visitdt trt trtDt

1 11/12/12 A 11/20/12

1 11/12/12 A 11/29/12

1 11/12/12 B 12/02/12

1 11/12/12 A 03/01/13

2 03/02/13 .

3 04/15/13 B 04/15/13

3 04/15/13 A 04/16/13

4 06/16/13 B 06/17/13

4 06/16/13 A 08/15/13

Any help is appreciated... thanks!

Re: Many-to-one association

art297 — Tue, 22 Oct 2013 14:30:33 GMT

There are probably numerous ways. Here is one:

data visits;

set visits;

set visits(firstobs=2 keep=visitdt rename=(visitdt=next_visitdt))

visits(firstobs=1 drop=_all_);

if missing(next_visitdt) then next_visitdt='31dec9999'd;

run;

proc sql;

create table want as

select *

from visits left join trt

on visitdt<=trtdt<next_visitdt

order by visitdt,trtdt

;

quit;

Re: Many-to-one association

Altal — Wed, 23 Oct 2013 03:12:04 GMT

Thanks very much!

topic Re: Many-to-one association in SAS Programming

Many-to-one association

Re: Many-to-one association

Re: Many-to-one association