topic Re: Idnetifying patients with more than one procedure per day in New SAS User

Identifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 13:22:01 GMT

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?

Re: Idnetifying patients with more than one procedure per day

Reeza — Wed, 08 Dec 2021 02:56:23 GMT

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?

Re: Idnetifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 02:02:50 GMT

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

Re: Idnetifying patients with more than one procedure per day

Reeza — Wed, 08 Dec 2021 02:56:59 GMT

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

Re: Idnetifying patients with more than one procedure per day

Astounding — Wed, 08 Dec 2021 03:04:52 GMT

You can separate the patients into two groups without COUNTER:

data just1 multiple;
set have;
by id date;
if first.date and last.date then output just1;
else output multiple;
run;

Re: Idnetifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 03:49:55 GMT

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

Thanks.

Re: Idnetifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 17:17:47 GMT

Hi @Astounding,

Your code works but generates 2 different data set. I need the data in the same data set file as new variables. I need to do proc freq/proc tabulate with the new data set.

Thanks

Re: Idnetifying patients with more than one procedure per day

Reeza — Wed, 08 Dec 2021 17:24:06 GMT

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.

Re: Idnetifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 18:06:31 GMT

@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.

Re: Idnetifying patients with more than one procedure per day

PaigeMiller — Wed, 08 Dec 2021 18:09:16 GMT

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??

Re: Idnetifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 18:20:06 GMT

@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,

Re: Idnetifying patients with more than one procedure per day

PaigeMiller — Wed, 08 Dec 2021 18:52:02 GMT

@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.

Re: Idnetifying patients with more than one procedure per day

Reeza — Wed, 08 Dec 2021 18:46:34 GMT

That's pretty trivial then.

If counter=2 then procedure2=procedure;

Re: Idnetifying patients with more than one procedure per day

PaigeMiller — Wed, 08 Dec 2021 18:49:08 GMT

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?

Re: Idnetifying patients with more than one procedure per day

Reeza — Wed, 08 Dec 2021 18:57:39 GMT

@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.

Re: Idnetifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 19:12:24 GMT

@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.

Re: Idnetifying patients with more than one procedure per day

PaigeMiller — Wed, 08 Dec 2021 19:49:07 GMT

@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.

Re: Idnetifying patients with more than one procedure per day

Reeza — Wed, 08 Dec 2021 19:54:06 GMT

Show what tables you want in the end and we'll help you get there with your current or appropriate data structure.

Re: Idnetifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 19:59:01 GMT

Hi @PaigeMiller ,

The data is form a larger data set. The first column is the PT_ID.

Re: Idnetifying patients with more than one procedure per day

Bluejags — Wed, 08 Dec 2021 20:04:23 GMT

@Reeza This is how the table should look:

Table of CONDX by CONDX2
CONDX(CONDX)- First procedure	CONDX2(CONDX2) –Second procedure
Frequency		ASD	Echo only	MI/BYPASS	MI/PERC	ToF	VALVE	VSD	Total
MI/BYPASS	50
MI/PERC	75	1
PDA	4
TGA	8
ToF	6
VALVE	82
VSD	2
Total