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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 386 views
  • 1 like
  • 2 in conversation