topic Re: creating a time window for event occurences in SAS Programming

creating a time window for event occurences

jins — Wed, 07 Feb 2024 16:18:34 GMT

Trying to get this manipulation going and any thoughts/help on this is appreciated.

Dataset Sample

id status date

1 OK 02-MAY-09

1 OK 10-MAY-09

1 DEL 01-JUL-22

1 OK 01-DEC-22

1 OK 04-DEC-22

1 DEL 06-DEC-22

2 DEL 01-JAN-23

3 OK 03-JAN-23

4 DEL 04-JAN-23

4 DEL 05-JAN-23

4 OK 06-JAN23

Looking for the following output which can be long or wide format

1 OK 02-MAY-09

1 DEL 01-JUL-22

1 OK 01-DEC-22

1 DEL 06-DEC-22

2 DEL 01-JAN-23

3 OK 03-JAN-23

4 DEL 04-JAN-23

4 OK 06-JAN23

Thanks in advance.

Re: creating a time window for event occurences

SASJedi — Wed, 07 Feb 2024 18:03:50 GMT

Can you explain the logic for choosing the rows to output?

Re: creating a time window for event occurences

jins — Wed, 07 Feb 2024 18:30:23 GMT

I should have mentioned that oops....

To obtain the date of the first occurrence between the status change by ID. For example ID-1 has two OK status before the DEL status, so we keep the first one with date 02-May-2009. For ID's with no status changes, it is output as is (ID-2).

Re: creating a time window for event occurences

jins — Wed, 07 Feb 2024 19:00:00 GMT

Is the notsorted option in the by variable a good idea to filter them out if the data is already sorted prior by id, date and status?

data check;

by id status notsorted;

if first.status=1;

run;

Re: creating a time window for event occurences

Patrick — Thu, 08 Feb 2024 01:37:53 GMT

Same selection logic as already suggested by @jins

data have;
  infile datalines truncover;
  input id status $ dt:date11.;
  format dt date9.;
  datalines;
1 OK 02-MAY-09
1 OK 10-MAY-09
1 DEL 01-JUL-22
1 OK 01-DEC-22
1 OK 04-DEC-22
1 DEL 06-DEC-22
2 DEL 01-JAN-23
3 OK 03-JAN-23
4 DEL 04-JAN-23
4 DEL 05-JAN-23
4 OK 06-JAN23
;

/* ensure source data is sorted as required */
proc sort data=have presorted;
  by id dt;
run;

data want;
  set have;
  by id status notsorted;
  if first.status;
run;

proc print data=want;
run;

Re: creating a time window for event occurences

jins — Thu, 08 Feb 2024 00:36:09 GMT

thanks.