Identifying patients with more than one procedure per day

[Bluejags]

ID	Date	Procedure	gender	Counter
1	08/01/2003	MI/BYPASS	F	1
2	08/01/2003	MI/PERC	F	2
3	12/03/2003	MI/PERC	F	1
4	12/03/2003	MI/BYPASS	F	2
5	08/01/2003	MI/PERC	F	1
6	08/02/2003	MI/BYPASS	M	1
7	08/02/2003	MI/PERC	M	2
8	08/04/2003	MI/BYPASS	F	1
9	08/04/2003	VALVE	F	1
10	08/05/2003	MI/PERC	F	1
11	08/04/2003	MI/BYPASS	F	1
12	08/04/2003	MI/PERC	F	2
13	08/04/2003	MI/PERC	F	1
14	08/06/2003	MI/BYPASS	M	1
15	08/06/2003	MI/PERC	M	2
16	08/06/2003	MI/PERC	F	1

Hello,

I have the above data, where the last column is a counter, that counts if more than one procedure is done on the same patient on the same day.  How do i identify patients that have more than one procedure per day and separate from patients with only one procedure per day?

[Reeza]

Please show what you want as the output.

---

@Bluejags wrote:

ID	Date	Procedure	gender	Counter
1	08/01/2003	MI/BYPASS	F	1
2	08/01/2003	MI/PERC	F	2
3	12/03/2003	MI/PERC	F	1
4	12/03/2003	MI/BYPASS	F	2
5	08/01/2003	MI/PERC	F	1
6	08/02/2003	MI/BYPASS	M	1
7	08/02/2003	MI/PERC	M	2
8	08/04/2003	MI/BYPASS	F	1
9	08/04/2003	VALVE	F	1
10	08/05/2003	MI/PERC	F	1
11	08/04/2003	MI/BYPASS	F	1
12	08/04/2003	MI/PERC	F	2
13	08/04/2003	MI/PERC	F	1
14	08/06/2003	MI/BYPASS	M	1
15	08/06/2003	MI/PERC	M	2
16	08/06/2003	MI/PERC	F	1

Hello,

I have the above data, where the last column is a counter, that counts if more than one procedure is done on the same patient on the same day.  How do i identify patients that have more than one procedure per day and separate from patients with only one procedure per day?

---

[Bluejags]

Hi Reeza,

I have the foll0wing data, where the last column is a counter, that counts if more than one procedure is done on the same patient on the same day. How do i identify patients that have more than one procedure per day and separate from patients with only one procedure per day?

Thanks

HAVE

ID	Date	Procedure	gender	Counter
1	08/01/2003	MI/BYPASS	F	1
1	08/01/2003	MI/PERC	F	2
2	12/03/2003	MI/PERC	F	1
2	12/03/2003	MI/BYPASS	F	2
3	08/01/2003	MI/PERC	F	1
4	08/02/2003	MI/BYPASS	M	1
4	08/02/2003	MI/PERC	M	2
5	08/04/2003	MI/BYPASS	F	1
6	08/04/2003	VALVE	F	1
7	08/05/2003	MI/PERC	F	1
8	08/04/2003	MI/BYPASS	F	1
8	08/04/2003	MI/PERC	F	2
9	08/04/2003	MI/PERC	F	1
10	08/06/2003	MI/BYPASS	M	1
10	08/06/2003	MI/PERC	M	2
11	08/06/2003	MI/PERC	F	1

WANT

ID	Date	Procedure	gender	Counter	Prcedure2
1	08/01/2003	MI/BYPASS	F	1	MI/BYPASS
1	08/01/2003	MI/PERC	F	2	MI/PERC
2	12/03/2003	MI/PERC	F	1	MI/PERC
2	12/03/2003	MI/BYPASS	F	2	MI/BYPASS
3	08/01/2003	MI/PERC	F	1
4	08/02/2003	MI/BYPASS	M	1	MI/BYPASS
4	08/02/2003	MI/PERC	M	2	MI/PERC
5	08/04/2003	MI/BYPASS	F	1
6	08/04/2003	VALVE	F	1
7	08/05/2003	MI/PERC	F	1
8	08/04/2003	MI/BYPASS	F	1	MI/BYPASS
8	08/04/2003	MI/PERC	F	2	MI/PERC
9	08/04/2003	MI/PERC	F	1
10	08/06/2003	MI/BYPASS	M	1	MI/BYPASS
10	08/06/2003	MI/PERC	M	2	MI/PERC
11	08/06/2003	MI/PERC	F	1

[Reeza]

Glad you reposted your data, your original data is quite different - IDs. 

Here are two solutions.

*SQL solution, single step;
proc sql;
create table want as 
select *, max(counter) as max_count,
case when calculated max_count>1 then procedure
end as proc2
from have
group by id
order by 1, 2;
quit;


*Data step solution, multiple steps;
data ids;
set have;
where counter > 1;
keep id;
run;

proc sort data=have;
by id;
run;

data want;
set have;
merge have ids(in=a);
by id;
if a then proc2=procedure;
run;

---

@Bluejags wrote:

Hi Reeza,

I have the foll0wing data, where the last column is a counter, that counts if more than one procedure is done on the same patient on the same day. How do i identify patients that have more than one procedure per day and separate from patients with only one procedure per day?

Thanks

HAVE

ID	Date	Procedure	gender	Counter
1	08/01/2003	MI/BYPASS	F	1
1	08/01/2003	MI/PERC	F	2
2	12/03/2003	MI/PERC	F	1
2	12/03/2003	MI/BYPASS	F	2
3	08/01/2003	MI/PERC	F	1
4	08/02/2003	MI/BYPASS	M	1
4	08/02/2003	MI/PERC	M	2
5	08/04/2003	MI/BYPASS	F	1
6	08/04/2003	VALVE	F	1
7	08/05/2003	MI/PERC	F	1
8	08/04/2003	MI/BYPASS	F	1
8	08/04/2003	MI/PERC	F	2
9	08/04/2003	MI/PERC	F	1
10	08/06/2003	MI/BYPASS	M	1
10	08/06/2003	MI/PERC	M	2
11	08/06/2003	MI/PERC	F	1

WANT

ID	Date	Procedure	gender	Counter	Prcedure2
1	08/01/2003	MI/BYPASS	F	1	MI/BYPASS
1	08/01/2003	MI/PERC	F	2	MI/PERC
2	12/03/2003	MI/PERC	F	1	MI/PERC
2	12/03/2003	MI/BYPASS	F	2	MI/BYPASS
3	08/01/2003	MI/PERC	F	1
4	08/02/2003	MI/BYPASS	M	1	MI/BYPASS
4	08/02/2003	MI/PERC	M	2	MI/PERC
5	08/04/2003	MI/BYPASS	F	1
6	08/04/2003	VALVE	F	1
7	08/05/2003	MI/PERC	F	1
8	08/04/2003	MI/BYPASS	F	1	MI/BYPASS
8	08/04/2003	MI/PERC	F	2	MI/PERC
9	08/04/2003	MI/PERC	F	1
10	08/06/2003	MI/BYPASS	M	1	MI/BYPASS
10	08/06/2003	MI/PERC	M	2	MI/PERC
11	08/06/2003	MI/PERC	F	1

---

[Bluejags]

Thanks @Reeza . Will try it tomorrow.  Also, can same procedure used to identify IDs with only 1 procedure?

Thanks.

[Reeza]

Again, you need to specify how you want that identified.

Maybe just add a flag to help yourself?

data labeled;
set have;
by id date;
if first.date and last.date then label="repeats";
else label="unique";
run;

---

@Bluejags wrote:

Thanks @Reeza . Will try it tomorrow.  Also, can same procedure used to identify IDs with only 1 procedure?

Thanks.

---

[Bluejags]

@Reeza . Sorry, I was confused too about how to go about the analysis.  Just got some clarification.  I need to have ONLY the second procedure done on the patient on the same day to be in the Procedure 2 column.  So, the table I WANT is:

ID	Date	Procedure	gender	Counter	Prcedure2
1	08/01/2003	MI/BYPASS	F	1
1	08/01/2003	MI/PERC	F	2	MI/PERC
2	12/03/2003	MI/PERC	F	1
2	12/03/2003	MI/BYPASS	F	2	MI/BYPASS
3	08/01/2003	MI/PERC	F	1
4	08/02/2003	MI/BYPASS	M	1
4	08/02/2003	MI/PERC	M	2	MI/PERC
5	08/04/2003	MI/BYPASS	F	1
6	08/04/2003	VALVE	F	1
7	08/05/2003	MI/PERC	F	1
8	08/04/2003	MI/BYPASS	F	1
8	08/04/2003	MI/PERC	F	2	MI/PERC
9	08/04/2003	MI/PERC	F	1
10	08/06/2003	MI/BYPASS	M	1
10	08/06/2003	MI/PERC	M	2	MI/PERC
11	08/06/2003	MI/PERC	F	1

Once again, sorry for not being clear on what I need.  Getting the ropes of SAS, and some times it is hard to understand what is needed exactlu.

[PaigeMiller]

Modifying the code from @Reeza 

data labeled;
set have;
by id date;
if not (first.date and last.date) then do;
    label="repeats";
    procedure2=procedure;
end;
else label="unique";
run;

Why do you need a variable PROCEDURE2 that is equal to PROCEDURE??

[Bluejags]

@PaigeMiller I need to have a column with 1st procedure done on all patients and a column for 2nd procedure done on the same day on the same patient to tabulate different what combination of procedures  were done and at what frequency.

Thanks,

[PaigeMiller]

---

@Bluejags wrote:

@PaigeMiller I need to have a column with 1st procedure done on all patients and a column for 2nd procedure done on the same day on the same patient to tabulate different what combination of procedures  were done and at what frequency.

---

This is not what you show in your desired output. In that desired output, you don't have the 1st procedure and the 2nd procedure in the same row, you have on the same row the 2nd procedure, and then a repeat of the 2nd procedure.

Can you clarify this?

Also, as a related issue, you do not have a Patient_ID variable, this seems to be to be an oversight that could easily allow a procedure on a 2nd patient to be mistakenly identified as the second procedure on one patient. Which could cause the entire logic to fail when we are using first.date and last.date. In fact, I think this causes the logic to fail in the data you have presented, and makes coming up with a unambiguous answer impossible with the data you have presented.

I now withdraw my earlier suggestion to use code based on first.date and last.date, as it will not work.

[Reeza]

That's pretty trivial then.

If counter=2 then procedure2=procedure;

[PaigeMiller]

Do you mean something like

If counter=2 then procedure2=lag(procedure);

yes, I know that doesn't exactly work, you can't put a LAG() inside an IF statement and expect it to work the way you want it to work, but is that the idea?

[Reeza]

---

@Bluejags wrote:

Once again, sorry for not being clear on what I need.  Getting the ropes of SAS, and some times it is hard to understand what is needed exactlu.

---

Coding is a tool to solve a problem, you need to understand what you want to do first for sure otherwise it's a very cumbersome process.

ID	Date	Procedure	gender	Counter	Prcedure2
1	08/01/2003	MI/BYPASS	F	1
1	08/01/2003	MI/PERC	F	2	MI/PERC
2	12/03/2003	MI/PERC	F	1
2	12/03/2003	MI/BYPASS	F	2	MI/BYPASS

I don't think this makes sense and is useful. What are you planning to do with this that makes you think you need your data like this in the first place?

You can get how many procs are first/second without any of this. So what are you trying to do overall, forget the code for now. 

---

@Bluejags wrote:

@Reeza . Sorry, I was confused too about how to go about the analysis.  Just got some clarification.  I need to have ONLY the second procedure done on the patient on the same day to be in the Procedure 2 column.  So, the table I WANT is:

ID	Date	Procedure	gender	Counter	Prcedure2
1	08/01/2003	MI/BYPASS	F	1
1	08/01/2003	MI/PERC	F	2	MI/PERC
2	12/03/2003	MI/PERC	F	1
2	12/03/2003	MI/BYPASS	F	2	MI/BYPASS
3	08/01/2003	MI/PERC	F	1
4	08/02/2003	MI/BYPASS	M	1
4	08/02/2003	MI/PERC	M	2	MI/PERC
5	08/04/2003	MI/BYPASS	F	1
6	08/04/2003	VALVE	F	1
7	08/05/2003	MI/PERC	F	1
8	08/04/2003	MI/BYPASS	F	1
8	08/04/2003	MI/PERC	F	2	MI/PERC
9	08/04/2003	MI/PERC	F	1
10	08/06/2003	MI/BYPASS	M	1
10	08/06/2003	MI/PERC	M	2	MI/PERC
11	08/06/2003	MI/PERC	F	1

Once again, sorry for not being clear on what I need.  Getting the ropes of SAS, and some times it is hard to understand what is needed exactlu.

---

[Bluejags]

@Reeza , I need to create (i) a column that contains the first procedure/day/patient ; (ii) a column the has the second procedure done on the same patient(s) on the same day as the first procedure.  Once I have these, I need to use proc tabulate to list out the procedures (first procedure) in a column against the list of second procedure done on the same patient; and get the counts. Say, frequency of MI/BYPASS, MI/PERC, etc as the first procedure and the frequency of Mi/BYPASS as the first and MI/PERC as the second procedure, and so on. i.e,. finding the number of times a combinations of surgery have been done, as opposed to the procedure being done only once.

Hope I am clear with my project objective.

[PaigeMiller]

---

@Bluejags wrote:

@Reeza , I need to create (i) a column that contains the first procedure/day/patient ; (ii) a column the has the second procedure done on the same patient(s) on the same day as the first procedure.  Once I have these, I need to use proc tabulate to list out the procedures (first procedure) in a column against the list of second procedure done on the same patient; and get the counts. Say, frequency of MI/BYPASS, MI/PERC, etc as the first procedure and the frequency of Mi/BYPASS as the first and MI/PERC as the second procedure, and so on. i.e,. finding the number of times a combinations of surgery have been done, as opposed to the procedure being done only once.

---

This is clear. However, I have serious concerns about doing this without some sort of Patient_ID in the data table, as I explained in my previous messages.