Creating a counter for the same hospitalization

[sarahsasuser]

Posts: 97

Creating a counter for hospitalizations

[ New ]

2 seconds ago

Hi All,

I have a large claims dataset where patients had a number of procedures on the same and different hospitalizations. I want to create a counter that counts the number of hospitalizations for each patient. This is easy to do if each row represents a different hospitalization, but many patients have several rows for the same hospitalization. How do I create this type of counter? The counter varaible Hospital_count is what I want to create in the data set below:

Want:

Patient      Hospital_admission_date   Procedure_code     Hospital_count

1               01/01/1999                         1.1                          1        

1               01/01/1999                         2.5                          1

1               02/01/1999                         2.3                          2

1               02/01/1999                         1.5                          2

2               01/01/1999                         2.2                          1

2               01/03/1999                         1.5                          2

[sarahsasuser]

Hi All,

I have a large claims dataset where patients had a number of procedures on the same and different hospitalizations. I want to create a counter that counts the number of hospitalizations for each patient. This is easy to do if each row represents a different hospitalization, but many patients have several rows for the same hospitalization. How do I create this type of counter? The counter varaible Hospital_count is what I want to create in the data set below:

Want:

Patient      Hospital_admission_date   Procedure_code     Hospital_count

1               01/01/1999                         1.1                          1        

1               01/01/1999                         2.5                          1

1               02/01/1999                         2.3                          2

1               02/01/1999                         1.5                          2

2               01/01/1999                         2.2                          1

2               01/03/1999                         1.5                          2

[RW9]

Well, the question is how do you know to increment the counter at row 3, I see nothing that would indicate the counter should change there.  You need to identify the logical check which will trigger a check.

[ballardw · Accepted solution]

I think this may get you started if I understand

data want;
   set have;
   by patient hospital_admission_date;
   retain count;
   If first.patient then count=1;
   else if first.hospital_admission_date then count+1;
run;

[sarahsasuser]

Thank you! such a simple and elegant solution that eluded me!

[sarahsasuser]

I have a follow up question. I now want to know how many procedures each patient had during their hospitalization. If the patient has two rows for the same hospitalization it means they had two different surgeries during the same hospitalization. Can you show me how to add procedure_count to the dataset below?

Want:

Patient      Hospital_admission_date   Procedure_code     Hospital_count      Procedure_count

1               01/01/1999                         1.1                          1                           2

1               01/01/1999                         2.5                          1                           2

1               02/01/1999                         2.3                          2                           2

1               02/01/1999                         1.5                          2                           2

2               01/01/1999                         2.2                          1                           1

2               01/03/1999                         1.5                          2                           1

[sarahsasuser]

Want:

Patient      Hospital_admission_date   Procedure_code     Hospital_count      Procedure_count

1               01/01/1999                         1.1                          1                           2

1               01/01/1999                         2.5                          1                           2

1               02/01/1999                         2.3                          2                           2

1               02/01/1999                         1.5                          2                           2

2               01/01/1999                         2.2                          1                           1

2               01/03/1999                         1.5                          2                           1

[sarahsasuser]

Want:

Patient      Hospital_admission_date   Procedure_code     Hospital_count      Procedure_count

1               01/01/1999                         1.1                          1                           2

1               01/01/1999                         2.5                          1                           2

1               02/01/1999                         2.3                          2                           2

1               02/01/1999                         1.5                          2                           2

2               01/01/1999                         2.2                          1                           1

2               01/03/1999                         1.5                          2                           1

[Ujjawal]

proc sql noprint;
create table tt as
select * from temp a inner join
(select patient, Hospital_admission_date ,count(*) as N
from temp
group by patient, Hospital_admission_date ) b
on a.patient=b.patient and a.Hospital_admission_date = b.Hospital_admission_date;
quit;

[sarahsasuser]

Is there a way I can do this in the data step instead of using sql? I have a number of data cleaning items and am outputting several datasets and would like to create the procedure counter within that data step.

[Jim_G]

try this:

data want;   set have;   by patient hospital_admission_date;   retain count procedures;  

    If first.patient then do; count=0; procedures=0; end;

     Procedures+1;

     If first hospital_admission_date then count+1;

     If last.patient then output; drop hospital_admission_date;

Jim