topic Re: How to create new variable count, that counts the total observation number within same idividua in SAS Programming

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

Hd1000204 — Thu, 05 Sep 2024 22:04:15 GMT

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.

Re: How to create new variable count, that counts the total observation number within same idividua

Tom — Fri, 06 Sep 2024 00:34:44 GMT

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?

Re: How to create new variable count, that counts the total observation number within same idividua

Ksharp — Fri, 06 Sep 2024 01:19:19 GMT

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;

Re: How to create new variable count, that counts the total observation number within same idividua

Hd1000204 — Fri, 06 Sep 2024 02:11:26 GMT

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.

Re: How to create new variable count, that counts the total observation number within same idividua

Hd1000204 — Fri, 06 Sep 2024 02:26:23 GMT

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.

Re: How to create new variable count, that counts the total observation number within same idividua

Tom — Fri, 06 Sep 2024 03:24:46 GMT

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.

Re: How to create new variable count, that counts the total observation number within same idividua

Ksharp — Fri, 06 Sep 2024 03:29:44 GMT

So did you try Tom's code ?

Re: How to create new variable count, that counts the total observation number within same idividua

Kurt_Bremser — Fri, 06 Sep 2024 11:14:55 GMT

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;

Re: How to create new variable count, that counts the total observation number within same idividua

Hd1000204 — Fri, 06 Sep 2024 15:44:59 GMT

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

Re: How to create new variable count, that counts the total observation number within same idividua

Hd1000204 — Fri, 06 Sep 2024 15:45:34 GMT

Yes! It works. Thank you for helping

Re: How to create new variable count, that counts the total observation number within same idividua

Hd1000204 — Fri, 06 Sep 2024 15:49:20 GMT

These codes also works! Thank you for helping