mamnipulatoinHello,
Could you please help me select the persons who had at least one visit in each 6 month period of the 24 month measure period, with
a munimum of 60 days between the first visit in the prior 6 month period and the last visit in the subsequent 6 month period? (The
2 years period is 3/1/2011-2/28/2013)
Here is the data:
id | Dates_of_visit |
1 | 28-Mar-11 |
1 | 22-Apr-11 |
2 | 5-Apr-11 |
2 | 31-May-11 |
2 | 3-Jun-11 |
2 | 3-Jun-11 |
2 | 9-Jun-11 |
2 | 25-Aug-11 |
2 | 12-Jan-12 |
2 | 21-Feb-12 |
2 | 18-May-12 |
2 | 20-Jul-12 |
2 | 18-Sep-12 |
2 | 28-Nov-12 |
3 | 22-Mar-11 |
3 | 26-Apr-11 |
3 | 12-Jul-11 |
3 | 27-Oct-11 |
3 | 15-May-12 |
3 | 29-May-12 |
3 | 19-Jun-12 |
4 | 16-Mar-11 |
4 | 8-Apr-11 |
4 | 15-Apr-11 |
4 | 6-Jul-11 |
4 | 28-Sep-11 |
4 | 11-Nov-11 |
4 | 27-Jan-12 |
4 | 23-Apr-12 |
4 | 23-Jul-12 |
4 | 19-Oct-12 |
4 | 23-Oct-12 |
5 | 25-May-11 |
5 | 12-Jan-12 |
5 | 12-Apr-12 |
5 | 23-May-12 |
5 | 3-Jul-12 |
5 | 6-Aug-12 |
5 | 25-Oct-12 |
5 | 12-Nov-12 |
5 | 16-Nov-12 |
6 | 16-Mar-11 |
6 | 11-May-11 |
6 | 17-Aug-11 |
7 | 31-Mar-11 |
7 | 3-May-11 |
7 | 24-Jun-11 |
7 | 25-Aug-11 |
7 | 25-Aug-11 |
7 | 22-Sep-11 |
7 | 10-Nov-11 |
7 | 8-Dec-11 |
7 | 9-Jan-12 |
7 | 16-Feb-12 |
7 | 30-Mar-12 |
7 | 10-May-12 |
7 | 7-Jun-12 |
7 | 12-Jul-12 |
7 | 9-Aug-12 |
7 | 27-Sep-12 |
7 | 15-Nov-12 |
7 | 6-Dec-12 |
7 | 6-Dec-12 |
8 | 3-Mar-11 |
8 | 24-Mar-11 |
8 | 24-Mar-11 |
8 | 24-May-11 |
8 | 3-Jun-11 |
8 | 15-Jun-11 |
8 | 15-Jun-11 |
8 | 28-Jun-11 |
8 | 6-Jul-11 |
8 | 15-Jul-11 |
8 | 29-Jul-11 |
8 | 4-Aug-11 |
8 | 18-Oct-11 |
8 | 3-Nov-11 |
8 | 19-Dec-11 |
8 | 3-Jan-12 |
8 | 27-Jan-12 |
8 | 8-Mar-11 |
8 | 24-Mar-11 |
8 | 24-Mar-11 |
8 | 28-Apr-11 |
8 | 28-Apr-11 |
8 | 26-Jul-11 |
8 | 9-Aug-11 |
8 | 11-May-11 |
8 | 21-Mar-12 |
8 | 16-Jul-12 |
8 | 10-Sep-12 |
8 | 13-Dec-12 |
9 | 10-Jun-11 |
9 | 5-Oct-11 |
10 | 23-Mar-11 |
10 | 6-Apr-11 |
10 | 13-Apr-11 |
10 | 1-Jun-11 |
10 | 24-Jan-12 |
10 | 8-Mar-12 |
10 | 5-Apr-12 |
10 | 13-Sep-12 |
10 | 19-Nov-12 |
10 | 28-Nov-12 |
11 | 14-Mar-11 |
12 | 25-Apr-11 |
12 | 23-May-11 |
12 | 5-Aug-11 |
12 | 9-Dec-11 |
12 | 13-Jan-12 |
12 | 2-Mar-12 |
12 | 6-Mar-12 |
13 | 30-Mar-11 |
13 | 14-Apr-11 |
13 | 15-Jun-11 |
13 | 25-Aug-11 |
13 | 27-Oct-11 |
13 | 23-Dec-11 |
13 | 27-Mar-12 |
13 | 7-Aug-12 |
13 | 17-Sep-12 |
13 | 26-Nov-12 |
14 | 30-Mar-11 |
14 | 16-May-11 |
14 | 24-Feb-12 |
14 | 25-May-12 |
14 | 5-Jul-12 |
14 | 28-Aug-12 |
14 | 3-Oct-12 |
14 | 18-Dec-12 |
15 | 17-Jan-12 |
15 | 15-Mar-12 |
15 | 30-Aug-12 |
15 | 30-Aug-12 |
15 | 28-Jan-13 |
16 | 21-Mar-11 |
16 | 23-May-11 |
16 | 29-Aug-11 |
16 | 28-Nov-11 |
16 | 29-Jun-12 |
16 | 22-Oct-12 |
16 | 12-Nov-12 |
17 | 11-May-11 |
17 | 25-May-11 |
17 | 14-Jun-11 |
17 | 6-Jul-11 |
17 | 9-Aug-11 |
17 | 20-Jun-12 |
17 | 20-Jun-12 |
17 | 6-Aug-12 |
17 | 21-Aug-12 |
17 | 18-Sep-12 |
17 | 23-Nov-12 |
17 | 30-Nov-12 |
18 | 7-Apr-11 |
18 | 18-Apr-11 |
18 | 18-Apr-11 |
18 | 3-May-11 |
18 | 6-May-11 |
18 | 2-Jun-11 |
18 | 12-Dec-11 |
19 | 14-Mar-11 |
19 | 27-Jun-11 |
19 | 11-Jul-11 |
19 | 30-Jan-12 |
19 | 21-Feb-12 |
19 | 21-May-12 |
19 | 11-Jun-12 |
19 | 19-Jul-12 |
19 | 11-Oct-12 |
19 | 25-Oct-12 |
19 | 6-Dec-12 |
19 | 6-Dec-12 |
20 | 13-Sep-11 |
20 | 25-Oct-11 |
20 | 17-Nov-11 |
21 | 31-Jul-12 |
21 | 24-Aug-12 |
21 | 26-Dec-12 |
21 | 30-Jan-13 |
21 | 25-Sep-12 |
22 | 8-Mar-11 |
22 | 5-Apr-11 |
22 | 3-May-11 |
22 | 31-May-11 |
22 | 7-Jun-11 |
22 | 28-Jun-11 |
22 | 28-Jun-11 |
22 | 26-Jul-11 |
22 | 26-Jul-11 |
22 | 23-Aug-11 |
22 | 23-Aug-11 |
22 | 20-Sep-11 |
22 | 18-Oct-11 |
22 | 15-Nov-11 |
22 | 13-Dec-11 |
22 | 10-Jan-12 |
22 | 7-Feb-12 |
22 | 6-Mar-12 |
22 | 17-Apr-12 |
22 | 15-May-12 |
22 | 12-Jun-12 |
22 | 10-Jul-12 |
22 | 7-Aug-12 |
22 | 4-Sep-12 |
22 | 2-Oct-12 |
22 | 30-Oct-12 |
22 | 30-Oct-12 |
22 | 27-Nov-12 |
22 | 27-Nov-12 |
22 | 27-Dec-12 |
23 | 31-May-11 |
23 | 28-Jun-11 |
23 | 1-Nov-11 |
23 | 15-Nov-11 |
23 | 15-Nov-11 |
23 | 14-Feb-12 |
23 | 15-May-12 |
23 | 12-Jun-12 |
23 | 31-Jul-12 |
23 | 28-Aug-12 |
23 | 25-Sep-12 |
23 | 12-Feb-13 |
24 | 16-Mar-11 |
24 | 16-Mar-11 |
24 | 9-Jun-11 |
24 | 8-Jul-11 |
24 | 24-Aug-11 |
24 | 7-Sep-11 |
24 | 9-Sep-11 |
24 | 19-Oct-11 |
24 | 21-Nov-11 |
24 | 21-Dec-11 |
24 | 2-Feb-12 |
24 | 2-Apr-12 |
24 | 2-May-12 |
24 | 2-Jul-12 |
24 | 2-Aug-12 |
24 | 2-Oct-12 |
24 | 1-Nov-12 |
24 | 29-Jan-13 |
25 | 2-Jun-11 |
25 | 2-Jun-11 |
25 | 12-Jul-11 |
25 | 6-Oct-11 |
25 | 11-Oct-11 |
26 | 8-Apr-11 |
26 | 11-Jul-11 |
26 | 13-Feb-12 |
26 | 9-Jul-12 |
26 | 7-Sep-12 |
27 | 16-Mar-11 |
28 | 13-Sep-11 |
28 | 25-Oct-11 |
28 | 17-Nov-11 |
29 | 14-Jul-11 |
29 | 3-Nov-11 |
29 | 13-Mar-12 |
29 | 12-Jun-12 |
29 | 4-Oct-12 |
29 | 27-Dec-12 |
30 | 29-Mar-11 |
30 | 30-Aug-11 |
30 | 4-Oct-11 |
30 | 8-Nov-11 |
30 | 6-Dec-11 |
30 | 6-Dec-11 |
30 | 3-Jan-12 |
30 | 3-Jan-12 |
30 | 31-Jan-12 |
30 | 8-Mar-12 |
30 | 17-Apr-12 |
30 | 15-May-12 |
30 | 17-Jul-12 |
30 | 31-Jul-12 |
30 | 25-Sep-12 |
30 | 9-Oct-12 |
30 | 8-Jan-13 |
30 | 5-Feb-13 |
Thank you so much!
OK - misread the requirement. Struggling to understand the context ( you want treatments that span at least 2 months but the treatment cycle must cross the (arbitrary) 3 month boundaries??) Well, if you insist
/* create indicator for 6 monthly periods */
Data periods ;
Set have ;
By Id Dates_of_Visit ;
Period = intck(‘semiyear.3’, ‘01mar2011’d, dates_of_visit) + 1 ;
Run ;
/* Screen for inter-period durations */
/* init is the first visit in the current period, start is the first visit in the previous period */
Data screen ;
Set Periods ;
Retain init start ;
By Id Period Dates_of_Visit ;
If first.Period then
init = Dates_of_Visit ;
If last.Period then
do ;
duration = Dates_of_Visit - start ; /* Values should be missing for the first period */
if duration >= 60 then output ;
start = init ; /* Set start ready for the next period */
end ;
Run ;
/* Select qualifying Ids */
Proc SQL ;
Create table want as
Select Id
From Screen
Having (Count(*) = 3)
Group By Id
;
Quit ;
[NB untested]
Richard
Do you wish to use a BASE SAS datastep; SQL; or some other style?
Essentially, you have to define the 6 month periods; define which rows fall into which six month period; then define the 60th day from the dates_of_visit for each row. Once you know the grouping and 60 day look ahead date for each row; then you compare each row to every other row for that ID. Ask the questions "does this ID for this row have another row within this 6 month period? If so, is the 60 day look ahead date less than or equal to the dates_of_visit for the row in the 6 month period.
Try this
/* create indicator2 for 6 monthly periods and gaps between visits */
Data periods ;
Set have ;
By Id Dates_of_Visit ;
Period = intck(‘semiyear.3’, ‘01mar2011’d, dates_of_visit) + 1 ;
Gap = Diff (Dates_of_Visit) ;
If First.id then call missing (Gap) ;
Run ;
/* Screen for inter-period gaps */
Data screen ;
Set Periods ;
By Id Period Dates_of_Visit ;
If First.Id
Or (First.Period and Gap >= 60)
;
Run ;
/* Select qualifying Ids */
Proc SQL ;
Create table want as
Select Id
From Screen
Having (Count(*) = 4)
Group By Id
;
Quit ;
[NB untested]
If you require the relevant dates against the Id then merge back to the screen dataset
Richard
Thank you Richard!
The gap defination is different from mine.
The gap should be 60 days or more between the first visit in the prior 6 month period and the last visit in the subsequent 6 month period.
OK - misread the requirement. Struggling to understand the context ( you want treatments that span at least 2 months but the treatment cycle must cross the (arbitrary) 3 month boundaries??) Well, if you insist
/* create indicator for 6 monthly periods */
Data periods ;
Set have ;
By Id Dates_of_Visit ;
Period = intck(‘semiyear.3’, ‘01mar2011’d, dates_of_visit) + 1 ;
Run ;
/* Screen for inter-period durations */
/* init is the first visit in the current period, start is the first visit in the previous period */
Data screen ;
Set Periods ;
Retain init start ;
By Id Period Dates_of_Visit ;
If first.Period then
init = Dates_of_Visit ;
If last.Period then
do ;
duration = Dates_of_Visit - start ; /* Values should be missing for the first period */
if duration >= 60 then output ;
start = init ; /* Set start ready for the next period */
end ;
Run ;
/* Select qualifying Ids */
Proc SQL ;
Create table want as
Select Id
From Screen
Having (Count(*) = 3)
Group By Id
;
Quit ;
[NB untested]
Richard
data have; set have; select; when (Dates_of_visit lt intnx('month','01mar2011'd,6)) period=1; when (Dates_of_visit lt intnx('month','01mar2011'd,12)) period=2; when (Dates_of_visit lt intnx('month','01mar2011'd,18)) period=3; when (Dates_of_visit lt intnx('month','01mar2011'd,24)) period=4; otherwise; end; run; proc sql; create table temp as select * from have group by id having count(distinct period)=4 order by id,period,Dates_of_visit ; quit; data temp1; set temp; by id period; if first.period ; period=period+1; run; data want; merge temp temp1(rename=(Dates_of_visit=_Dates_of_visit)); by id period; if last.period then do; if (Dates_of_visit-_Dates_of_visit) ge 60 then output; end; run; proc sort data=want(keep=id) out=final nodupkey;by id;run;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.