topic Re: Creating a counter for the same hospitalization in SAS Data Management

Creating a counter for the same hospitalization

sarahsasuser — Wed, 08 Jun 2016 15:13:30 GMT

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

Re: Creating a counter for the same hospitalization

RW9 — Wed, 08 Jun 2016 15:32:39 GMT

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.

Re: Creating a counter for the same hospitalization

ballardw — Wed, 08 Jun 2016 15:51:12 GMT

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;

Re: Creating a counter for the same hospitalization

sarahsasuser — Wed, 08 Jun 2016 15:55:21 GMT

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

Re: Creating a counter for the same hospitalization

sarahsasuser — Fri, 10 Jun 2016 17:01:14 GMT

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

Re: Creating a counter for the same hospitalization

Ujjawal — Fri, 10 Jun 2016 17:56:04 GMT

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;

Re: Creating a counter for the same hospitalization

sarahsasuser — Mon, 13 Jun 2016 14:40:39 GMT

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.

Re: Creating a counter for the same hospitalization

Jim_G — Mon, 20 Jun 2016 19:58:36 GMT

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