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 | . |
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 |
okay - updated the data!
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;
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
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;
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.