Help using Base SAS procedures

Grabbing observations within a date range from long data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Grabbing observations within a date range from long data

Dealing mainly with the following varaibles:

 

Case_ind

Dateofvisit

PersonID

 

What i have done is define with other variables whether or not a doctor visit was related to my condition of interest (Case_ind 0/1). Now i need to pull out all other visits for the same person that are within 3 weeks before or after the visit for the condititon of interest (case_ind=1). I know how to do this with only 3 weeks after by using a retain statement in a data set by personID, dateofvisit, and case_ind but I am not sure how to grab the ones 3 weeks before?

 

Any help would be greatly appreciated!!


Accepted Solutions
Solution
Wednesday
Grand Advisor
Posts: 10,026

Re: Grabbing observations within a date range from long data

data have;
   informat obsid Case_ind best. Dateofvisit mmddyy10. Personid best.;
   format Dateofvisit mmddyy10. ; 
   input obsid Case_ind Dateofvisit Personid ;
datalines;
1 0 1/1/2017 1 
2 0 1/4/2017 1 
3 0 2/1/2017 1 
4 0 3/20/2017 1 
5 0 3/24/2017 1 
6 1 4/3/2017 1 
7 0 4/3/2017 1 
8 0 4/4/2017 1 
9 0 4/20/2017 1 
10 0 5/11/2017 1 
11 0 5/31/2017 1 
12 0 7/25/2017 1 
13 0 9/1/2017 1 
14 0 9/2/2017 1 
;
run;

proc sql;
   create table want as
   select b.*
   from (select * from have where case_ind=1) as a
        left join
        have as b
        on a.Personid=b.Personid
   where b.dateofvisit between intnx('week',a.dateofvisit,-3,'s') and 
                               intnx('week',a.dateofvisit,+3,'s')
   order by b.personid,b.Dateofvisit
   ;
quit;

Should do the basic time request.

 

You will have to provide an exampled data set, in the form of a data step similar to above, with an example of that "One thing I should mention is someone could have a case of the condition more than once, so we would need to take out the observations that are within 3 weeks of both cases even though they are for the same person." AND exactly what the result should look like.

 

There are so many ways that dates could overlap that you may be providing a number of examples. I'm envisioning Case_ind at two week intervals two or more times.

 

It may be that all that would be needed is Select Distinct b.* in the code above to remove duplicates depending on what your overlap rules actually entail.

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,052

Re: Grabbing observations within a date range from long data

Can you provide an example of the dataset you have, and (based on that example) what you want?

 

You can probably do it using a DOW loop, but I'd rather see what you have before suggesting code.

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 15

Re: Grabbing observations within a date range from long data

Here is a very simple example of what I am looking for (data are confidential so I cannot provide an actual example).

 

Input data set:

OBS IDCase_indDateofvisitPersonid
101/1/20171
201/4/20171
302/1/20171
403/20/20171
503/24/20171
614/3/20171
704/3/20171
804/4/20171
904/20/20171
1005/11/20171
1105/31/20171
1207/25/20171
1309/1/20171
1409/2/20171

 

Ideal output dataset:

OBS IDCase_indDateofvisitPersonid
403/20/20171
503/24/20171
614/3/20171
704/3/20171
804/4/20171
904/20/20171

 

This process would need to be repeated for all of the people in the dataset. One thing I should mention is someone could have a case of the condition more than once, so we would need to take out the observations that are within 3 weeks of both cases even though they are for the same person.

 

Thank you!!

Solution
Wednesday
Grand Advisor
Posts: 10,026

Re: Grabbing observations within a date range from long data

data have;
   informat obsid Case_ind best. Dateofvisit mmddyy10. Personid best.;
   format Dateofvisit mmddyy10. ; 
   input obsid Case_ind Dateofvisit Personid ;
datalines;
1 0 1/1/2017 1 
2 0 1/4/2017 1 
3 0 2/1/2017 1 
4 0 3/20/2017 1 
5 0 3/24/2017 1 
6 1 4/3/2017 1 
7 0 4/3/2017 1 
8 0 4/4/2017 1 
9 0 4/20/2017 1 
10 0 5/11/2017 1 
11 0 5/31/2017 1 
12 0 7/25/2017 1 
13 0 9/1/2017 1 
14 0 9/2/2017 1 
;
run;

proc sql;
   create table want as
   select b.*
   from (select * from have where case_ind=1) as a
        left join
        have as b
        on a.Personid=b.Personid
   where b.dateofvisit between intnx('week',a.dateofvisit,-3,'s') and 
                               intnx('week',a.dateofvisit,+3,'s')
   order by b.personid,b.Dateofvisit
   ;
quit;

Should do the basic time request.

 

You will have to provide an exampled data set, in the form of a data step similar to above, with an example of that "One thing I should mention is someone could have a case of the condition more than once, so we would need to take out the observations that are within 3 weeks of both cases even though they are for the same person." AND exactly what the result should look like.

 

There are so many ways that dates could overlap that you may be providing a number of examples. I'm envisioning Case_ind at two week intervals two or more times.

 

It may be that all that would be needed is Select Distinct b.* in the code above to remove duplicates depending on what your overlap rules actually entail.

Occasional Contributor
Posts: 15

Re: Grabbing observations within a date range from long data

This worked! Sorry for the late response, testing the code out on my huge dataset took awhile. Multiple cases for a person had to be six weeks apart, so this code works with the multiple case scenario. Thank you so much!!!!
Occasional Contributor
Posts: 15

Re: Grabbing observations within a date range from long data

I'm hoping you can answer a post-solution question. Is there a way to add an indicatior to that code? I was hoping to have another indicator that tells me if the office visit was before or after the visit with the diagnosis (i.e. if the office visit occured before I would have place=-1 and if it happened after place=1). I have been messing around with codes but i cant get anything to work!

 


Thank you!

Occasional Contributor
Posts: 14

Re: Grabbing observations within a date range from long data

[ Edited ]

Since you said you have the 3 week time period after the condition of interest handled, you can do a similar process for the 3 weeks before.

 

As of now, the data is sorted by PersonID and Dateofvisit.  You need to sort by PersonID and descending Dateofvisit and repeat the methodology you used to pull of the visits within 3 weeks. This will get the 3 weeks prior to the condition of interest.

 

I.e.

 

proc data data = test out = reverse_order;
by PersonID descending Dateofvisit;
run;

 

*Add the code you used to pull out the other visits within the 3 weeks time period backward;
data filter_3weeks_prior_condition;
set reverse_order;
/*Filter dataset here*/
run;

 

*Then, stack the 2 pulled out datasets, 3 weeks forward and 3 weeks backward;
data entire_condition_period;
set filter_3weeks_prior_condition
filter_3weeks_after_condition /*Your 3 weeks after condition dataset*/;
run;

 

*Sort the data again to get into proper order (ascending);
proc sort data = entire_condition_period;
by PersonID Dateofvisit;
run;

 

You will now have all of the visits 3 weeks prior and after for the IDs with the condition of interest.

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 104 views
  • 0 likes
  • 4 in conversation