topic Counting specific observations from observations for a patient with a condition in SAS Procedures

Counting specific observations from observations for a patient with a condition

am12scorp — Thu, 28 May 2020 23:06:41 GMT

For the table below, I would like to count observations for each patient till days_betn_clms is less than or equal to 14. If days_betn_clms is GE 14 then a new episode of treatment starts and would like to again count claims for the new episode till 'days_betn_clms' is LE 14.

Patient_id	Claim_date	Lag_date	Days_betn_clms
1	05/13/2016	.	1
1	06/01/2016	05/13/2016	19
2	04/10/2015	.	1
2	4/14/2015	04/10/2015	4
2	4/21/2015	4/14/2015	7
2	10/22/2015	4/21/2015	184
2	10/30/2015	10/22/2015	8
2	11/04/2015	10/30/2015	5
3	08/09/2010	.	1
3	08/16/2010	08/09/2010	7
3	08/23/2010	08/16/2010	7
3	08/30/2010	08/23/2010	7
3	09/01/2010	08/30/2010	2
3	09/10/2010	09/01/2010	9
3	06/24/2011	09/10/2010	284
3	06/27/2011	06/24/2011	3
3	06/28/2011	06/27/2011	1
3	07/15/2011	06/28/2011	17

I would like to see the new data set to be created as below:

Patient_id	Count
1	1
1	1
2	3
2	3
3	6
3	3
3	1

Appreciate any help! Thanks in advance.

Re: Counting specific observations from observations for a patient with a condition

novinosrin — Thu, 28 May 2020 23:34:58 GMT

Hi @am12scorp Fun stuff

data have;
input 
Patient_id

Claim_date :mmddyy10.

Lag_date :mmddyy10.

Days_betn_clms
;
format 	Claim_date mmddyy10. 
Lag_date mmddyy10.;
cards;
1

05/13/2016

.

1

1

06/01/2016

05/13/2016

19

2

04/10/2015

.

1

2

4/14/2015

04/10/2015

4

2

4/21/2015

4/14/2015

7

2

10/22/2015

4/21/2015

184

2

10/30/2015

10/22/2015

8

2

11/04/2015

10/30/2015

5

3

08/09/2010

.

1

3

08/16/2010

08/09/2010

7

3

08/23/2010

08/16/2010

7

3

08/30/2010

08/23/2010

7

3

09/01/2010

08/30/2010

2

3

09/10/2010

09/01/2010

9

3

06/24/2011

09/10/2010

284

3

06/27/2011

06/24/2011

3

3

06/28/2011

06/27/2011

1

3

07/15/2011

06/28/2011

17
;

data want;
 _iorc_=1;
 if _n_=1 then do;
  dcl hash H (ordered: "A") ;
  h.definekey  ("_iorc_") ;
  h.definedata ("patient_id", "count") ;
  h.definedone () ;
  dcl hiter hi('h');
 end;
 do _n_=h.clear() by 0 until(last.patient_id);
  set have;
  by patient_id;
  if Days_betn_clms le 14 then  count=sum(count,1);
  else do; count=1;	_iorc_+1;end;
  h.replace();
 end;
 do while(hi.next()=0);
  output;
 end;
 keep patient_id count;
run;

RESULTS:

Re: Counting specific observations from observations for a patient with a condition

novinosrin — Thu, 28 May 2020 23:42:24 GMT

Hi @am12scorp again. If you are unfamiliar with Hashes, the following is much simpler 2 step solution.


data have;
input 
Patient_id

Claim_date :mmddyy10.

Lag_date :mmddyy10.

Days_betn_clms
;
format 	Claim_date mmddyy10. 
Lag_date mmddyy10.;
cards;
1

05/13/2016

.

1

1

06/01/2016

05/13/2016

19

2

04/10/2015

.

1

2

4/14/2015

04/10/2015

4

2

4/21/2015

4/14/2015

7

2

10/22/2015

4/21/2015

184

2

10/30/2015

10/22/2015

8

2

11/04/2015

10/30/2015

5

3

08/09/2010

.

1

3

08/16/2010

08/09/2010

7

3

08/23/2010

08/16/2010

7

3

08/30/2010

08/23/2010

7

3

09/01/2010

08/30/2010

2

3

09/10/2010

09/01/2010

9

3

06/24/2011

09/10/2010

284

3

06/27/2011

06/24/2011

3

3

06/28/2011

06/27/2011

1

3

07/15/2011

06/28/2011

17
;

data temp;
 grp=1;
 do until(last.patient_id);
  set have;
  by patient_id;
  if Days_betn_clms le 14 then  count=sum(count,1);
  else do; count=1;	grp+1;end;
  output;
 end;
run;

data  want;
 set temp;
 by patient_id grp;
 if last.grp;
 keep  patient_id count;
run;

RESULTS:

Re: Counting specific observations from observations for a patient with a condition

am12scorp — Fri, 29 May 2020 01:28:56 GMT

@novinosrin this is magic. Thank you for your quick help!