Solved
New Contributor
Posts: 3

# How do I select last obs of first consecutive obs

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

Accepted Solutions
Solution
‎09-05-2017 05:02 AM
Super User
Posts: 9,376

## Re: How do I select last obs of first consecutive obs

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;```

All Replies
Super User
Posts: 9,376

## Re: How do I select last obs of first consecutive obs

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.

New Contributor
Posts: 3

## Re: How do I select last obs of first consecutive obs

[ Edited ]

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.

Solution
‎09-05-2017 05:02 AM
Super User
Posts: 9,376

## Re: How do I select last obs of first consecutive obs

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;```
New Contributor
Posts: 3

## Re: How do I select last obs of first consecutive obs

Thanks so much RW9! Exactly what I meant

Frequent Contributor
Posts: 113

## Re: How do I select last obs of first consecutive obs

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

data sample;
set sample;
by id;
if first.id then do;
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.
☑ This topic is solved.