topic Re: Flagging first change in 6-12 month span in SAS Programming

Flagging first change in 6-12 month span

kt_080517 — Tue, 13 Nov 2018 19:39:27 GMT

Person	Test	Test_date	status
1	12	4/8/2015	3
1	23	7/7/2015	3
1	34	4/28/2016	3
1	45	10/21/2016	3
1	56	10/21/2016	3
2	22	9/1/2016	3
2	33	10/1/2016	3
2	44	3/1/2017	2
2	55	8/1/2017	3
3	88	10/1/2015	3
3	99	11/1/2015	2
3	76	5/1/2016	3
4	87	5/10/2015	3
4	86	11/10/2015	3
4	85	11/20/2015	3
4	84	1/5/2016	3
4	83	2/16/2016	3
4	82	3/1/2016	2
4	81	4/1/2017	3

I want to flag a change in status - I want to flag the first change that happens in a span of 6-12 months. If there is no change in status, I will keep the second test greater than or equal to 6 months. Essentially, I want to keep 2 records per person - the first ever record and the second record that reports a change (or no change).

Expected flags:

Person	Test	Test_date	status	flag
1	12	4/8/2015	3	.
1	23	7/7/2015	3	.
1	34	4/28/2016	3	1
1	45	10/21/2016	3	.
1	56	10/21/2016	3	.
2	22	9/1/2016	3	.
2	33	10/1/2016	3	.
2	44	3/1/2017	2	1
2	55	8/1/2017	3	.
3	88	10/1/2015	3	.
3	99	11/1/2015	2	.
3	76	5/1/2016	3	1
4	87	5/10/2015	3	.
4	86	11/10/2015	3
4	85	11/20/2015	3	.
4	84	1/5/2016	3	.
4	83	2/16/2016	3	.
4	82	3/1/2016	2	1
4	81	4/1/2017	3	.

Re: Flagging first change in 6-12 month span

novinosrin — Tue, 13 Nov 2018 19:34:46 GMT

Can you plz post the expected output for the input sample you posted?

+ a Note stating the logic

And some dates are not in ascending order like

3	99	11/1/2015	2
3	76	5/1/2015	3

Re: Flagging first change in 6-12 month span

kt_080517 — Tue, 13 Nov 2018 19:39:53 GMT

okay - updated the data!

Re: Flagging first change in 6-12 month span

Ksharp — Wed, 14 Nov 2018 13:38:52 GMT

How about this one ?

data have;
infile cards expandtabs truncover;
input Person	Test	Test_date :mmddyy12.	status;
format test_date mmddyy10.;
cards;
1	12	4/8/2015	3
1	23	7/7/2015	3
1	34	4/28/2016	3
1	45	10/21/2016	3
1	56	10/21/2016	3
2	22	9/1/2016	3
2	33	10/1/2016	3
2	44	3/1/2017	2
2	55	8/1/2017	3
3	88	10/1/2015	3
3	99	11/1/2015	2
3	76	5/1/2016	3
4	87	5/10/2015	3
4	86	11/10/2015	3
4	85	11/20/2015	3
4	84	1/5/2016	3
4	83	2/16/2016	3
4	82	3/1/2016	2
4	81	4/1/2017	3
;
run;
data want;
do i=1 by 1 until(last.person);
 set have;
 by person;
 if first.person then do;_status=status;_test_date=test_date;end;
 if _status ne status and not found then do;
   found=1;	_i=i;
   n=intck('month',_test_date,test_date,'c');
 end;
end;

do i=1 by 1 until(last.person);
 set have;
 by person;
 flag=.;
 if 6<=n<=12 and _i=i then do;f=1;flag=1;end;
  else if  intck('month',_test_date,test_date,'c')>=6 and not f then do;f=1;flag=1;end;
 output;
end;
drop _: i f n found;
run;

Re: Flagging first change in 6-12 month span

kt_080517 — Wed, 14 Nov 2018 15:13:33 GMT

Thank you for this! I ran the code - and I think the one issue I have is with the flags for in which there are two or tests within the 6-12 mo period. I want to only have one flag (for the first instance that a test falls within the 6-12 months). The code you provided adds multiple flags..

So for example say I have this additional client, your code would flag both tests (1/13/2016 AND 1/19/2016), I'd only want to flag the 1/13/2016 record.

9	12	2/5/2015	39	23	1/13/2016	39	34	1/19/2016	39	45	2/24/2016	3

Re: Flagging first change in 6-12 month span

Ksharp — Thu, 15 Nov 2018 12:43:21 GMT

OK. I know where is the problem.

Try this one again.

data have;
infile cards expandtabs truncover;
input Person	Test	Test_date :mmddyy12.	status;
format test_date mmddyy10.;
cards;
1	12	4/8/2015	3
1	23	7/7/2015	3
1	34	4/28/2016	3
1	45	10/21/2016	3
1	56	10/21/2016	3
2	22	9/1/2016	3
2	33	10/1/2016	3
2	44	3/1/2017	2
2	55	8/1/2017	3
3	88	10/1/2015	3
3	99	11/1/2015	2
3	76	5/1/2016	3
4	87	5/10/2015	3
4	86	11/10/2015	3
4	85	11/20/2015	3
4	84	1/5/2016	3
4	83	2/16/2016	3
4	82	3/1/2016	2
4	81	4/1/2017	3
9	12	2/5/2015	3
9	23	1/13/2016	3
9	34	1/19/2016	3
9	45	2/24/2016	3
;
run;


data want;
do i=1 by 1 until(last.person);
 set have;
 by person;
 if first.person then do;_status=status;_test_date=test_date;end;
 if _status ne status and not found then do;
   found=1;	_i=i;
   n=intck('month',_test_date,test_date,'c');
 end;
end;

do i=1 by 1 until(last.person);
 set have;
 by person;
 flag=.;
 if 6<=n<=12 then do; if _i=i then do;f=1;flag=1;end; end; /*<------*/
  else if  intck('month',_test_date,test_date,'c')>=6 and not f then do;f=1;flag=1;end;
 output;
end;
drop _: i f n found;
run;