SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating a counter for the same hospitalization

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 106
Accepted Solution

Creating a counter for the same hospitalization

 
Posts: 97
 
Creating a counter for hospitalizations
[ New ]
 

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


Accepted Solutions
Solution
‎06-08-2016 11:54 AM
Super User
Posts: 11,343

Re: Creating a counter for the same hospitalization

Posted in reply to sarahsasuser

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,988

Re: Creating a counter for the same hospitalization

Posted in reply to sarahsasuser

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.

Solution
‎06-08-2016 11:54 AM
Super User
Posts: 11,343

Re: Creating a counter for the same hospitalization

Posted in reply to sarahsasuser

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;
Frequent Contributor
Posts: 106

Re: Creating a counter for the same hospitalization

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

Frequent Contributor
Posts: 106

Re: Creating a counter for the same hospitalization

Posted in reply to 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

Regular Contributor
Posts: 184

Re: Creating a counter for the same hospitalization

Posted in reply to sarahsasuser

 

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;

 

Frequent Contributor
Posts: 106

Re: Creating a counter for the same hospitalization

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.

Frequent Contributor
Posts: 95

Re: Creating a counter for the same hospitalization

[ Edited ]
Posted in reply to sarahsasuser

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 629 views
  • 2 likes
  • 5 in conversation