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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.