Transposing and Calculating

[stancemcgraw]

Trying to calculate the total number of days between cases of "Debridement" by study number. 

Data Have:

Studynum            Proc1                         Procedure Date

4                          Debridement            09SEP13:15:34:00

4                          Debridement            12SEP13:14:45:00

4                           Debridement           20SEP13:20:22:00

5                         Debridement              25OCT14:13:01:00

5                         Debridement              26OCT14:15:01:00

Data Want:

Studynum        Total Days Between Cases of Debridement

4                        5.3 

5                        2.1

[ballardw]

---

@stancemcgraw wrote:

Trying to calculate the mean number of days between cases of "Debridement" or "DI" (Debridement and Irrigation) by study number. 

Data Have:

Studynum            Proc1                         Procedure Date

4                          Debridement            09SEP13:15:34:00

4                           DI                           11SEP13:13:20:00

4                          Debridement            12SEP13:14:45:00

4                           Debridement           20SEP13:20:22:00

5                         DI                               23OCT14:23:22:00

5                         Debridement              25OCT14:13:01:00

Data Want:

Studynum        Mean Days Between Cases of Debridement

4                        5.3 

5                        2.1

---

And how would you get 5.3 for the first? is that by computing the absolute difference of the the single DI datetime to all of the Debridement dates and then averaging?

Do any of your studynum values have more than one DI datetime? If so describe how to calculate the individual differences that would be used for a mean?

[stancemcgraw]

DI and Debridement will be counted as the same thing. So yes. I just put 5.3 there is an example. I don't know the actual number

[PaigeMiller]

Please explain the logic/math/algorithm used to determine 5.3 for studynum 4 and 2.1 for studynum 5 (neither of which are obvious to me, even in the simpler case of studynum 5)

[stancemcgraw]

I did not actually calculate that. I just put those numbers as an example. I'm trying to figure out how to calculate the total days between

[PaigeMiller]

But we don't know how we would perform this calculation for studynum 4. You have to explain how it is done.

For studynum 5, it would appear the answer is 1 day, 13 hours, 39 minutes, or the decimal equivalent. Is that correct?

[stancemcgraw]

I would like to create SAS code that calculates the total days between cases of debridement by studynum. I don't know how to do that.

[PaigeMiller]

And as @ballardw and I have stated, we don't know what that means in the case of studynum 4. We can't tell you how to do this. We are asking for an explanation of what the calculations are, not the SAS code needed. Once we understand what the calculations are, we can determine the SAS code.

I think you need to talk to some subject matter expert at your company/university who can explain how to compute the average in the case of studynum 4.

I would also like you to state that the answer I gave for studynum 5 is correct (or not).

[novinosrin · Accepted solution]

hi @stancemcgraw   Are you after this?

data have;
input Studynum            Proc1     : $20.                  Procedure_Date :datetime20.;
format      Procedure_Date datetime20.;
cards;
4                          Debridement            09SEP13:15:34:00
4                           DI                           11SEP13:13:20:00
4                          Debridement            12SEP13:14:45:00
4                           Debridement           20SEP13:20:22:00
5                         DI                               23OCT14:23:22:00
5                         Debridement              25OCT14:13:01:00
;

data want;
set have;
by Studynum;
k=datepart(Procedure_Date);
diffindays=dif(k);
if first.studynum then diffindays=0;
drop k;
run;

[ballardw]

Similar approach to @novinosrin but maintaining the seconds for the calculation. @stancemcgraw did not provide any rules about calculating anything. So I am getting the successive difference of seconds and averaging that. I leave the (should be trivial) exercise of turning the number of seconds into "days".

Issues in defining the calculations that should be addressed:

1) which values are to be compared. One "date" with the previous only or is some date compared with multiples?

2) is the data sorted in ascending date within the studynum? If not how should negative differences be treated? Or should the data be sorted to prevent that.

3) Are the differences supposed to be based on actual time difference or on "days" between values? If "days" between values describe how to calculate a partial day. The order of operations and approach on this step will make BIG differences in interpretation of results.

data have;
 input Studynum   Proc1 :$12.  ProcedureDate :datetime18.;
 format ProcedureDate datetime18.;
datalines;
4  Debridement  09SEP13:15:34:00
4  Debridement  12SEP13:14:45:00
4  Debridement  20SEP13:20:22:00
5  Debridement  25OCT14:13:01:00
5  Debridement  26OCT14:15:01:00
;
run;

data temp;
   set have;
   by studynum;
   ddt = dif(proceduredate);
   if first.studynum then ddt=.;
run;

Proc summary data=temp nway;
   class studynum;
   var ddt;
   output out=work.summary mean=;
run;
/* at this time the ddt is mean of successive datetime differences 
IN SECONDS
*/

proc print data=work.summary ;
   var studynum ddt;
   format ddt time12.;
run; 

The time format will show the mean number of hours, minutes and seconds in this case.

[Reeza]

---

@stancemcgraw wrote:

I would like to create SAS code that calculates the total days between cases of debridement by studynum. I don't know how to do that.

---

Let's look at Study number 4. You have 3 numbers there so what is the total days?

1. Is it from the first day to the last day,
2. Or is the length between each treatment,
3. Or is it length between each treatment averaged?

The technical issue here is trivial, but the logic of how it should be calculated is what we would need to know to properly answer this question. 

[novinosrin]

@ all respondents,

I think OP is figuring out the requirement while we are pondering the same. Sure, our responses are gonna get the OP more confused while business experts themselves perhaps do not communicate well to the tech team. Lately, it seems the industry absolutely needs people to be familiar TECH+BUSINESS which apparently is hard to find.

Anyways, lunch break 2:00 CST