Collapse command

[Martin Imelda Borg]

OK, I would like to keep all the records of the individuals belgoning to the same IDno
whereby the admidate is less than the opdate

Therefore:
IDno 1 : Records episode1, 2
IDno 2: Records episode 1-3
IDno 3: Records episode 1-2
IDno 4: Records episode 1, Episode 2-4 (for procuedre 115 )

1. I tried
collapse (max) MI dvt, by (IDno procedureid)
But then this wouldn't give me IDno4 episode 2-4

Appreciate if someone can help and suggest perhaps a better syntax
I also tried filtering if opdate >admindate (however the issue is that it would include the missing values).
I tried substituting with 0 but this would automatically generate a 1960 date.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(procedureid surgicalindex IDno episodeno admidate dvt MI opdate4)
  . 0 1 1   0 1 0   .
110 1 1 2  31 1 0  32
  . 0 2 1  60 0 1   .
  . 1 2 2 305 1 1   .
112 0 2 3 335 0 1 336
  . 0 3 1 366 0 1   .
113 1 3 2 397 0 1 397
  . 1 3 3 425 0 1   .
  . 0 3 4 456 0 1   .
  . 0 3 5 486 0 1   .
114 1 4 1 517 1 0 518
  . 0 4 2 548 1 0   .
  . 0 4 3 580 1 1   .
115 1 4 4 612 1 1 613
  . 0 4 5 643 1 1   .
end
format %td admidate
format %td opdate4

[Ken Chui]

Would be great if you can clarify the followings:

• In the text you mentioned ID 1, 2, 3, and 4. But in the data, there are only 110-115. Could you reconcile them to better express what's wanted?
• These seem like repeated record but then the ID is not repeated. Could you double check and make sure the example reflects the structure of your actual data?
• From your text, it implies that opdate4 should not missing and should be the same number within each ID. Please validate and revise your data example.

[Martin Imelda Borg]

So Yes IDno would be 1-4 - specific code for the individual
110 - 115 is referring to procedureid i.e an intervention done to an individual

So in general,
If an individual (IDno) came to clinic, this would be recorded as an episodeno.
If a procedure was done - this would be recorded as (procedureid) + date of the procedure - opdate ; and as episodeno
If a individual came to clinic but had nothing done for eg had a blood test - this would be recorded as episodeno only

Is that enough to clarify ?

[Ken Chui]

So Yes IDno would be 1-4 - specific code for the individual
110 - 115 is referring to procedureid i.e an intervention done to an individual

So in general,
If an individual (IDno) came to clinic, this would be recorded as an episodeno.
If a procedure was done - this would be recorded as (procedureid) + date of the procedure - opdate ; and as episodeno
If a individual came to clinic but had nothing done for eg had a blood test - this would be recorded as episodeno only

Is that enough to clarify ?

Thanks, I think I got it, but still not sure about the missings in opdate4.

According to #1, for IDno2, all three cases should be retained. But according #3, it would seem that only case 3 should be retained because the first two cases had no procedure done (hence no date).

Should IDno 2 have "02dec1960" across all 3 records? That's the logic I'm not getting.

[Martin Imelda Borg]

So IDno2 - episodes 1-3 should be retained as the opdate in 3 > admidate
(A) The episodes 1-2 has no intervention done therefore are coded as missing , therefore no the 2Dec1960 shouldn't be across 3 records

But of course if I use the logic in A - it won't work as a missing is an infinity - when I ask to keep the records whereby opdate> admin date and then run the collapse syntax in #1.
If I just run the collapse syntax #1, this won't work as it will not take into consideration Individual4 who had 2 procedures on 2 different opdates


The following is what I want my data to look like
As soon can see Patient 3 - episodes 3-5 are gone as these are after the procedure
Individual 4 - episode 5 is lost as this is after the procedure. As you can see this individual has had 2 procedures (see procedureid) , so all the epsidoes prior to the operationdate need to be kept


This is how i would like to look like (manually done)

* Example generated by -dataex-. For more info, type help dataex
clear
input float(procedureid surgicalindex IDno episodeno admidate dvt MI opdate4) str11 this
  . 0 1 1   0 1 0   . "keep"       
110 1 1 2  31 1 0  32 "keep"       
  . 0 2 1  60 0 1   . "keep"       
  . 1 2 2 305 1 1   . "keep"       
112 0 2 3 335 0 1 336 "keep"       
  . 0 3 1 366 0 1   . "keep"       
113 1 3 2 397 0 1 397 "keep"       
  . . . .   . . .   . "don't keep" 
  . . . .   . . .   . "don't keep" 
  . . . .   . . .   . "don't keep" 
114 1 4 1 517 1 0 518 "keep "      
  . 0 4 2 548 1 0   . "keep "      
  . 0 4 3 580 1 1   . "keep "      
115 1 4 4 612 1 1 613 "keep"       
  . . . .   . . .   . "don't keep "
end
format %td admidate
format %td opdate4

[Ken Chui]

Try this:

bysort IDno: egen tempdate = max(opdate4)
keep if tempdate > admidate
drop tempdate

[Martin Imelda Borg]

Nice one, and i just saw myself over complicating matters, perhaps as i want to do two steps in one.
Should learn to break down my target into steps. thanks