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;

 

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
  • 5 replies
  • 1004 views
  • 0 likes
  • 3 in conversation