Hi there!
I'm trying to create a new variable "count" that counts how many visits were within each period.
For example: My original data looks like this:
id hosp_adm_date period_end
1 11/10/2017 11/10/2018
1 01/12/2018 01/12/2019
1 02/09/2018 02/09/2019
1 02/17/2018 02/17/2019
1 02/28/2018 02/28/2019
1 03/15/2018 03/15/2019
1 04/09/2018 04/09/2019
2 04/09/2016 04/09/2015
2 04/09/2018 04/09/2018
I want to create table something like this:
id hosp_adm_date period_end counts
1 11/10/2017 11/10/2018 6
1 01/12/2018 01/12/2019 5
1 02/09/2018 02/09/2019 4
1 02/17/2018 02/17/2019 3
1 02/28/2018 02/28/2019 2
1 03/15/2018 03/15/2019 1
1 04/09/2019 04/09/2020 1
2 04/09/2016 04/09/2015 1
2 04/09/2018 04/09/2018 1
Ex. For id 1's first period 11/10/2017- 11/10/2018 , there will be 6 visits within them.
How can I do that? I have tried several codes but not working. I would be really appreciate if someone can help with this.
So you want to count re-admissions that occurred within one year of the original admission?
Did you try something like this?
data have;
input id admdate :mmddyy. ;
format admdate yymmdd10.;
cards;
1 11/10/2017
1 01/12/2018
1 02/09/2018
1 02/17/2018
1 02/28/2018
1 03/15/2018
1 04/09/2018
2 04/09/2016
2 04/09/2018
;
proc sql;
create table want as
select a.*,count(b.id) as n_readmission
from have a
left join have b
on a.id=b.id
and b.admdate between (a.admdate+1) and intnx('year',a.admdate,1,'s')
group by a.id,a.admdate
;
quit;
Result
Obs id admdate n_readmission 1 1 2017-11-10 6 2 1 2018-01-12 5 3 1 2018-02-09 4 4 1 2018-02-17 3 5 1 2018-02-28 2 6 1 2018-03-15 1 7 1 2018-04-09 0 8 2 2016-04-09 0 9 2 2018-04-09 0
Notice how the last observations have zero re-admissions. If you want those to have 1 instead then you could remove the +1 in the ON condition. But then all of the counts will go up by one since you will be counting the current admission record every time.
From your problem description I was expecting you to share TWO input datasets. One with the PERIODS and the other with the ADMISSION dates. Why do you only show ONE dataset? How can there be more than one ADMISSION per hospital stay?
Thank you for your question. I apologize for not explaining the dataset clearly
This dataset captures all hospital admission events for different subjects in a long-form format. Each row represents a unique admission event. The "period end" is calculated as 12 months after each unique hospital admission date for a specific ID.
We're working with a single dataset that contains multiple admissions per subject. What I'm trying to determine is how many events occurred within each 12-month period following a unique hospital admission date for each subject.
There aren't two separate datasets for periods and admission dates. Instead, we're using the admission dates to define the start of each period, and then counting events within those periods.
So you want to count re-admissions that occurred within one year of the original admission?
Did you try something like this?
data have;
input id admdate :mmddyy. ;
format admdate yymmdd10.;
cards;
1 11/10/2017
1 01/12/2018
1 02/09/2018
1 02/17/2018
1 02/28/2018
1 03/15/2018
1 04/09/2018
2 04/09/2016
2 04/09/2018
;
proc sql;
create table want as
select a.*,count(b.id) as n_readmission
from have a
left join have b
on a.id=b.id
and b.admdate between (a.admdate+1) and intnx('year',a.admdate,1,'s')
group by a.id,a.admdate
;
quit;
Result
Obs id admdate n_readmission 1 1 2017-11-10 6 2 1 2018-01-12 5 3 1 2018-02-09 4 4 1 2018-02-17 3 5 1 2018-02-28 2 6 1 2018-03-15 1 7 1 2018-04-09 0 8 2 2016-04-09 0 9 2 2018-04-09 0
Notice how the last observations have zero re-admissions. If you want those to have 1 instead then you could remove the +1 in the ON condition. But then all of the counts will go up by one since you will be counting the current admission record every time.
data have;
input id (hosp_adm_date period_end ) (: mmddyy12.);
format hosp_adm_date period_end mmddyy10.;
cards;
1 11/10/2017 11/10/2018
1 01/12/2018 01/12/2019
1 02/09/2018 02/09/2019
1 02/17/2018 02/17/2019
1 02/28/2018 02/28/2019
1 03/15/2018 03/15/2019
1 04/09/2018 04/09/2019
2 04/09/2015 04/09/2016
2 04/09/2018 04/09/2018
;
proc sql;
create table want as
select *,(select count(*) from have where id=a.id and hosp_adm_date between a.hosp_adm_date and a.period_end) as count
from have as a
order by 1,2;
quit;
Use a date-indexed array and a double DO loop:
data have;
input id admdate :mmddyy. ;
format admdate yymmdd10.;
cards;
1 11/10/2017
1 01/12/2018
1 02/09/2018
1 02/17/2018
1 02/28/2018
1 03/15/2018
1 04/09/2019
2 04/09/2016
2 04/09/2018
;
%let start = %sysfunc(inputn(1900-01-01,yymmdd10.));
%let end = %sysfunc(inputn(2099-12-31,yymmdd10.));
data want;
array ev {&start.:&end.} _temporary_;
do i = &start. to &end.;
ev{i} = 0;
end;
do until (last.id);
set have;
by id;
ev{admdate} = 1;
end;
do until (last.id);
set have;
by id;
n_readmission = 0;
do i = admdate to intnx("year",admdate,1,"s");
n_readmission + ev{i};
end;
output;
end;
drop i;
run;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.