BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Altal
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

2 REPLIES 2
art297
Opal | Level 21

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;

Altal
Calcite | Level 5

Thanks very much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 2 replies
  • 803 views
  • 0 likes
  • 2 in conversation