topic Re: keep first obs per id and first obs after 6 months in SAS Programming

keep first obs per id and first obs after 6 months

kt_080517 — Tue, 02 Oct 2018 17:47:08 GMT

SubjectID	FormDate	Status	Lag_formdate	interval_months	form_num
1	3/24/2015	2	.	.	1
1	7/7/2015	3	3/24/2015	4	2
1	4/14/2016	3	7/7/2015	9	3
1	7/7/2016	3	4/14/2016	3	4
1	10/12/2016	3	7/7/2016	3	5
3	8/12/2015	3	.	.	1
3	11/17/2015	3	8/12/2015	3	2
3	4/5/2016	3	11/17/2015	5	3
3	5/12/2016	3	4/5/2016	1	4
5	1/16/2015	2	.	.	1
5	2/13/2015	2	1/16/2015	1	2
5	9/2/2015	2	2/13/2015	7	3
5	5/20/2016	3	9/2/2015	8	4
5	12/9/2016	3	5/20/2016	7	5
7	10/15/2015	1	.	.	1
7	4/14/2016	3	10/15/2015	6	2
7	10/27/2016	3	4/14/2016	6	3
8	4/6/2015	3	.	.	1
8	1/21/2016	3	4/6/2015	9	2
8	8/23/2016	3	1/21/2016	7	3

Dataset A is above.

I want to keep the first form per subject and the first form that comes <= 6 months; so there are 2 forms per client in the output dataset.

I have calculated the lag form date and presented in a column above. I would use the lag date minus the form date to get the first assessment after 6 months.

I need help getting the first form after 6 months.

data B;
set A;
by subjectID formdate;

if first.subjectID;

run;

Re: keep first obs per id and first obs after 6 months

novinosrin — Tue, 02 Oct 2018 17:53:57 GMT

Can you plz post the expected output for your input sample

Re: keep first obs per id and first obs after 6 months

kt_080517 — Tue, 02 Oct 2018 17:57:47 GMT

SubjectID	FormDate	Status	Lag_formdate	interval_months	form_num
1	3/24/2015	2	.	.	1
1	4/14/2016	3	7/7/2015	9	3
3	8/12/2015	3	.	.	1
3	4/5/2016	3	11/17/2015	5	3
5	1/16/2015	2	.	.	1
5	9/2/2015	2	2/13/2015	7	3
7	10/15/2015	1	.	.	1
7	4/14/2016	3	10/15/2015	6	2
8	4/6/2015	3	.	.	1
8	1/21/2016	3	4/6/2015	9	2

Re: keep first obs per id and first obs after 6 months

kt_080517 — Tue, 02 Oct 2018 17:58:48 GMT

I would actually just use formdate NOT lag_formdate to determine the 6 months interval

Re: keep first obs per id and first obs after 6 months

novinosrin — Tue, 02 Oct 2018 18:06:28 GMT

I am not quite getting the 6 months interval in my head, can you explain how you derive that in your sample.

I beg your pardon, my brain is not functioning well today

Re: keep first obs per id and first obs after 6 months

kt_080517 — Tue, 02 Oct 2018 19:31:14 GMT

The first form I want is the earliest form for a subject, and then I would take the next form that comes after 6 months. For example, say a subject has 3 forms entered on the following dates: 01/01/2018, 03/01/2018, and 08/01/2018. The first form would be dated 01/01/2018, and then the next form would be the one that comes right after a 6 month window, so the form entered on 08/01/2018. Make sense?

Re: keep first obs per id and first obs after 6 months

novinosrin — Tue, 02 Oct 2018 19:57:24 GMT

Assuming I understand, kindly verify the code below

data have1;
input SubjectID	FormDate :mmddyy10.	Status	Lag_formdate  :mmddyy10.	interval_months	form_num;
format formdate Lag_formdate mmddyy10.;
cards;
1	3/24/2015	2	.	.	1
1	7/7/2015	3	3/24/2015	4	2
1	4/14/2016	3	7/7/2015	9	3
1	7/7/2016	3	4/14/2016	3	4
1	10/12/2016	3	7/7/2016	3	5
3	8/12/2015	3	.	.	1
3	11/17/2015	3	8/12/2015	3	2
3	4/5/2016	3	11/17/2015	5	3
3	5/12/2016	3	4/5/2016	1	4
5	1/16/2015	2	.	.	1
5	2/13/2015	2	1/16/2015	1	2
5	9/2/2015	2	2/13/2015	7	3
5	5/20/2016	3	9/2/2015	8	4
5	12/9/2016	3	5/20/2016	7	5
7	10/15/2015	1	.	.	1
7	4/14/2016	3	10/15/2015	6	2
7	10/27/2016	3	4/14/2016	6	3
8	4/6/2015	3	.	.	1
8	1/21/2016	3	4/6/2015	9	2
8	8/23/2016	3	1/21/2016	7	3
;

data want;
set have1;
by SubjectID;
retain _t _f;
if first.SubjectID then do;
output;
_t=FormDate;
_f=0;
end;
else _k=intck('month',_t,FormDate);
if _k>=6 and not _f then do;
output;
_f=1;
end;
drop _:;
run;