Idnetifying patients with more than one procedure per day

[Bluejags]

Hi @PaigeMiller ,

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

[PaigeMiller]

---

@Bluejags wrote:

Hi @PaigeMiller ,

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

---

No, the first column of your data is NOT the patient ID. If it were, then no patient ever had a second procedure. We need to have data and explanation match before we can help further.

[Reeza]

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

[Bluejags]

@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

[Reeza]

Are you sure you want that picture? Why do you have Frequency labelled above a column that isn't a count? Where do the headers for the columns come from?

[Bluejags]

@Reeza , I need to get the frequency table (as given from you) derived form the earlier data, where we need to create the columns having proc1 and proc2. The dummy data set is what I had posted earlier.

Thanks.

[Reeza]

So the code I posted doesn't answer your question? Did you run it and check the WIDE data set?
If it doesn't please explain in detail how it does not.

[Bluejags]

@Reeza that works perfectly with the dummy data set.  I will try with the full data set and keep you posted.

Thanks.

[Reeza · Accepted solution]

Makes your fake data - in the future please provide this step similar as shown.

I was too lazy to fix the data structure that gets messed up when copy/paste so went with it. You should not need this step in your process.

data have;
informat ID $8. date mmddyy10. procedure $20. gender $1. Counter 8.;
input
ID
/Date
/Procedure
/gender
/Counter

;
cards; 
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
;;;;

Here's the analysis part.

*sort;
proc sort data=have;
by id date gender;
run;

*transpose to appropriate structure;
proc transpose data=have out=wide prefix=proc;
by id date gender;
id counter;
var procedure;
run;

*summarize;
proc freq data=wide;
table proc1*proc2 / nopercent norow nocol sparse missing;
run;

And here's the output based on your input data:

[Bluejags]

@Reeza Worked like Charm, on the big data too.  Thanks, again.  I greatly appreciate your time and effort. How do i improve my sas skills? what kind of analytical  thinking is needed?

Also thanks to @PaigeMiller and @Astounding  who had responded.  Each one of you have given me a better insight. Thanks.

[Reeza]

In this particular case, this is an exact example of this:

https://en.wikipedia.org/wiki/XY_problem

My process for all problems (not just coding), what do I have, what do I want, and what's the limitations on getting there. Also note your assumptions clearly - ie only one ID per record and ensure you verify them. Then you can start to problem solve it. 

For programming, this means showing what my input data is, what output I need, then I try and break it down into steps for each using pseudocode. Once I understand the problem and can pseudocode it then I start actually coding. Coding in my last step in solving a problem. After 15+ years I don't need  to do that for most of the problems here.

And some of it is just experience and my luck in having a very good memory. 

Good Luck!

[Bluejags]

@Reeza . You showed the easy way. Can we achieve the same results by Array/Proc summary?  I ask this as these has procedures have been alternative method for transposing data.

Thanks.

[Reeza]

I think PROC SUMMARY is an option but I don't think arrays are good here. Depending on how far much you wanted to do with the arrays. You would need to know all the different combinations and dimensions ahead of time for the array method and I prefer more dynamic coding that's flexible to change as your data changes. 

@Tom is the PROC SUMMARY expert so he can probably comment on that approach better than I can. It may still require the data transposition though. 

---

@Bluejags wrote:

@Reeza . You showed the easy way. Can we achieve the same results by Array/Proc summary?  I ask this as these has procedures have been alternative method for transposing data.

Thanks.

---

[Astounding]

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;

[Bluejags]

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