How to create new variable count, that counts the total observation number within same idividuals?

[Hd1000204]

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. 

[Tom]

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?

[Hd1000204]

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.

[Tom · Accepted solution]

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.

[Hd1000204]

Thank you soooo much! I modified a little bit based on your code and it works

[Ksharp]

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;

[Hd1000204]

Thank you for your help, I've tried the code but the count result isn't exactly what I'm looking for. I've provided more details about my exact requirements in replies to previous threads. Thanks again for your assistance.

[Ksharp]

So did you try Tom's code ?

[Hd1000204]

Yes! It works. Thank you for helping

[Kurt_Bremser]

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;

[Hd1000204]

These codes also works! Thank you for helping