How do I select last obs of first consecutive obs

[yvonne1]

Hi,

I would like some help in selecting the last date of the first series of consecutive observations (Sas enterprise 6.1). Currently my (large) dataset is comprised of dates (numeric) per ID. Example:

OBS	ID	Date
1	1	20150316
2	1	20150317
3	1	20150318
4	1	20150326
5	1	20150327
6	1	20150328
7	1	20150329
8	1	20150330
9	1	20150331
10	2	20150601
11	2	20150602
12	2	20150603
13	2	20150624
14	2	20150625
15	2	20150907
16	2	20150908
17	3	20150127
18	3	20150128
19	3	20150129
20	3	20150130
21	3	20150131

And would like my output to look like:

OBS	ID	Date
1	1	20150318
2	2	20150603
3	3	20150131

(dates for obs 3, 12 and 21 )

I know how to select the first obs per ID and last obs per ID but not this..

Hopefully someone can help me! Thanks

[RW9]

You say you want to pull out the last date in a group of data, however you have not shown any grouping information, so this is not possible.  Why should:

1

20150318

 This data item be the one pulled out from the data?  I can see no logical reason for it from what you have posted, so its not possible to provide logic to cover the scenario.

[yvonne1]

Hi

Thanks for the quick reply, as you might see there are consecutive dates and non consecutive data (dates following each other) within each ID. They are hospital admission dates where subsequent dates equal one admission and when there is more than 1 day in between a new hospital admission is registered per person.

So for person 1 (ID=1) the first admission is from 16 to 18th of march (obs 1 2 and 3), the second 26 to 31st of march (obs 4 to 9)

There is no variable indication these seperate admissions.So I would like to first identify the first admission (series of dates) and then the last day of this first admission.

Is this helpful?

[RW9 · Accepted solution]

Ah, ok, then maybe something like the below (note how I have the test data, please use this method in future).  What this does is assign a period to each block, assuming the dates are sequential, then simply takes min/max of each period to give the output of id period and start/stop dates:

data have;
  input n id date;
  format date date9.;
  informat date yymmdd8.;
datalines;
1	1	20150316
2	1	20150317
3	1	20150318
4	1	20150326
5	1	20150327
6	1	20150328
7	1	20150329
8	1	20150330
9	1	20150331
10	2	20150601
11	2	20150602
12	2	20150603
13	2	20150624
14	2	20150625
15	2	20150907
16	2	20150908
;
run;
data inter;
  set have;
  by id;
  retain period lstdate;
  if first.id then do;
    period=1;
    lstdate=date;
  end;
  else if date ne lstdate+1 then period=period+1;
  lstdate=date;
run;
proc sql;
  create table WANT as
  select  ID,
          PERIOD,
          min(DATE) as FIRST_DATE format=date9.,
          max(DATE) as LAST_DATE format=date9.
  from    INTER
  group by ID,PERIOD;
quit;

[yvonne1]

Thanks so much RW9! Exactly what I meant

[ShiroAmada]

proc sort data=sample;
by id date;
run;

data sample;
set sample;
format lag_admit_dt date9.;
by id;
lag_admit_dt=lag(date);
diff=intck('days',lag_admit_dt,date);
if first.id then do;
lag_admit_dt=admit_dt;
diff=1;
tag='';
end;

if diff>1 then tag='*';
retain tag;
run;

data First_Discharge_dt (keep=id date);
set sample (where = (tag is missing) ) ;
by id;
if last.id;
run;

NOTE: I added ID=4 for patient with 1 day admission date only.