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.
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:
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:
@novinosrin this is magic. Thank you for your quick help!
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.
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.
Ready to level-up your skills? Choose your own adventure.