BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kt_080517
Calcite | Level 5
SubjectIDFormDateStatusLag_formdateinterval_monthsform_num
13/24/20152..1
17/7/201533/24/201542
14/14/201637/7/201593
17/7/201634/14/201634
110/12/201637/7/201635
38/12/20153..1
311/17/201538/12/201532
34/5/2016311/17/201553
35/12/201634/5/201614
51/16/20152..1
52/13/201521/16/201512
59/2/201522/13/201573
55/20/201639/2/201584
512/9/201635/20/201675
710/15/20151..1
74/14/2016310/15/201562
710/27/201634/14/201663
84/6/20153..1
81/21/201634/6/201592
88/23/201631/21/201673

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;

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

Can you plz post the expected output for your input sample

kt_080517
Calcite | Level 5
SubjectIDFormDateStatusLag_formdateinterval_monthsform_num
13/24/20152..1
14/14/201637/7/201593
38/12/20153..1
34/5/2016311/17/201553
51/16/20152..1
59/2/201522/13/201573
710/15/20151..1
74/14/2016310/15/201562
84/6/20153..1
81/21/201634/6/201592
kt_080517
Calcite | Level 5
I would actually just use formdate NOT lag_formdate to determine the 6 months interval
novinosrin
Tourmaline | Level 20

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

kt_080517
Calcite | Level 5

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? 

novinosrin
Tourmaline | Level 20

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 882 views
  • 2 likes
  • 2 in conversation