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
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;
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.
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;
Thank you! such a simple and elegant solution that eluded me!
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
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;
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.
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.