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!
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!
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.