BookmarkSubscribeRSS Feed
am12scorp
Calcite | Level 5

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.

 

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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:

results.PNG

novinosrin
Tourmaline | Level 20

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:

results.PNG

 

am12scorp
Calcite | Level 5

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 834 views
  • 1 like
  • 2 in conversation