AE overlap programming query

[abhinayingole]

Hi,

I need to create SUBJOIN variable as shown below.

I have information till JOIN variable.

the dataset sorted by USUBJID AE ASTDT AENDT JOIN.

JOIN variable reflects the overlapping Adverse Event.

we need to created SUBJOIN variable for every USUBJID  AE JOIN group.

if the AENDT date of the prior AE is not the ASTDT date of the next AE, then we need to create SUBJOIN as +1.

If the start date of the next AE is the end date of the previous AE record then AJOINSID remains the same. (that is why we have SUBJOIN = 1 for obs. no. 5, as the 17JAN2023 is the end date for obs. no. 2)

first and last obs. have value 0 because they don't have any overlapping event.

could you please help me program this?

I am also attaching the datalines for this dataset for reference.

data test;
informat astdt date9. aendt date9.;
input @1 usubjid $3.
@5 astdt date9.
@15 aendt date9.
@25 ae $5.
@31 join 1.
;

format astdt aendt date9.;

datalines;
128	03JAN2023 10JAN2023	cough 1
128	27DEC2022 17JAN2023	vomit 1
128	03JAN2023 17JAN2023	vomit 1
128	10JAN2023 24JAN2023	vomit 1
128	17JAN2023 28FEB2023	vomit 1
128	31JAN2023 14FEB2023	vomit 1
128	31JAN2023 14FEB2023	vomit 1
128	21FEB2023 28FEB2023	vomit 1
128	28FEB2023 21MAR2023	vomit 1
128	11APR2023 .	        vomit 2
;
run;

[Kurt_Bremser]

Per your description, subjoin for observation 2 should be 0, as a new ae group starts.

[abhinayingole]

No Kurt, as obs. no. 2 to 9 are the overlapping AEs with AE = vomit and JOIN = 1, so they need subgrouping, which is why they need to start with 1.

Please let me know if this information helps.

[Kurt_Bremser]

So it actually means: if there is only one observation, then subjoin = 0, otherwise we start with 1.

But why is subjoin = 1 in obs 9? It belongs to the same group, and the start date is equal to the previous end date, so it should continue with 6.

And what is the exact rule for obs 5 getting 1?

[abhinayingole]

Hi Kurt,

Thanks for the reply.

yes, you are correct with this statement....So it actually means: if there is only one observation, then subjoin = 0, otherwise we start with 1.

SUBJOIN value obs. no. 9 and 5 are 1 because of this rule....

If the start date of the next AE is the end date of the previous AE record then AJOINSID remains the same.

so in case of obs. no. 9. ASTDT =28FEB2023 is the end date of 5th obs. (i.e AENDT = 28FEB2023) so the SUBJOIN value will be the whatever value is from 5th obs. in this case obs. no . 5 SUBJOIN =1 so it will be same for obs. no. 9.

in case of obs. no. 5. ASTDT =17JAN2023 is the end date of 2nd obs. (i.e AENDT = 17JAN2023) so the SUBJOIN value will be the whatever value is from 2nd obs. in this case obs. no . 2 SUBJOIN =1 so it will be same for obs. no. 5.

[Kurt_Bremser · Accepted solution]

Try this:

data want;
set test;
by usubjid ae join;
length subjoin 8 maxjoin 8;
retain maxjoin;
if _n_ = 1
then do;
  declare hash end ();
  end.definekey("aendt");
  end.definedata("subjoin");
  end.definedone();
end;
if first.join and last.join
then subjoin = 0;
else do;
  if first.join
  then do;
    rc = end.clear();
    maxjoin = 1;
    subjoin = 1;
    rc = end.add();
  end;
  else do;
    if end.find(key:astdt) ne 0
    then do;
      maxjoin + 1;
      subjoin = maxjoin;
      rc = end.add();
    end;
    else do;
      if end.check() ne 0
      then rc = end.add();
    end;
  end;
end;
drop rc maxjoin;
run;

[abhinayingole]

Thanks Kurt , it worked 🙂

[Kurt_Bremser]

Since the code uses a hash object, which is a rather advanced feature of the DATA step, feel free to ask if it's not entirely clear to you.

[abhinayingole]

Hi Kurt, 

Yes, sure... I was also looking for more on DECLARE HASH.

Surely will get back to you if more information needed. Really appreciate your help.

However, I need your help with one more variable with this data. I will start different discussion for this.

Hope to see you there 🙂