BookmarkSubscribeRSS Feed
kt_080517
Calcite | Level 5
PersonTestTest_datestatus
1124/8/20153
1237/7/20153
1344/28/20163
14510/21/20163
15610/21/20163
2229/1/20163
23310/1/20163
2443/1/20172
2558/1/20173
38810/1/20153
39911/1/20152
3765/1/20163
4875/10/20153
48611/10/20153
48511/20/20153
4841/5/20163
4832/16/20163
4823/1/20162
4814/1/20173

 

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:

PersonTestTest_datestatusflag
1124/8/20153.
1237/7/20153.
1344/28/201631
14510/21/20163.
15610/21/20163.
2229/1/20163.
23310/1/20163.
2443/1/201721
2558/1/20173.
38810/1/20153.
39911/1/20152.
3765/1/201631
4875/10/20153.
48611/10/20153 
48511/20/20153.
4841/5/20163.
4832/16/20163.
4823/1/201621
4814/1/20173.
5 REPLIES 5
novinosrin
Tourmaline | Level 20

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
kt_080517
Calcite | Level 5

okay - updated the data!

Ksharp
Super User

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;
kt_080517
Calcite | Level 5

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

 

 

Ksharp
Super User

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;

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1597 views
  • 0 likes
  • 3 in conversation