topic Re: Data Manipulation in SAS Programming

Data Manipulation

0529wen — Tue, 20 May 2014 19:54:29 GMT

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!

Re: Data Manipulation

jwillis — Tue, 20 May 2014 20:11:31 GMT

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.

Re: Data Manipulation

RichardinOz — Tue, 20 May 2014 21:37:02 GMT

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

Re: Data Manipulation

Ksharp — Wed, 21 May 2014 14:47:37 GMT

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

Re: Data Manipulation

0529wen — Thu, 22 May 2014 14:06:15 GMT

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.

Re: Data Manipulation

RichardinOz — Thu, 22 May 2014 15:19:35 GMT

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