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 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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.

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