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!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1147 views
  • 1 like
  • 2 in conversation