LAG function help

[Emma2021]

I have a long data. I would like to keep all rows if the lag date is more than 5 days, else keep only the first row, for example:

Have:
id date
1 1jul2021
1 1jul2021
1 4jul2021
1 10jul2021
1 16jul2021
1 18jul2021

Wanted:
id date
1 1jul2021
1 10jul2021
1 16jul2021


Thank you!

[PaigeMiller]

data have;
input id date :date9.;
format date date9.;
cards;
1 1jul2021
1 1jul2021
1 4jul2021
1 10jul2021
1 16jul2021
1 18jul2021
;
data want;
    set have;
    prev_date=lag(date);
    if _n_=1 or date-prev_date>5 then output;
    drop prev_date;
run;

And if there are additional ID values, then what? Please present a data set with multiple IDs and the correct output.

When you present data sets, please follow my example above and provide working SAS data step code, rather than just text as you did — Thanks.

[Emma2021]

I have another twist--I have a "flag" variable. If not missing then get the row that associated with smallest date's row (see 1 4jul2021 1), but take if not missing and the flag variable is earliest date not with smallest flag value (see 1 16jul2021 2 although the 18jul2021 has smaller flag=1 ) . How can I do below? 

Have:
id date flag
1 1jul2021 .
1 1jul2021 .
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1

2 1jul2021 1

2 6jul2021 .

Wanted:
id date flag
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2

2 1jul2021 1

2 6jul2021 .

[PaigeMiller]

As I requested, please post your example data as SAS data step code (as in my earlier example) and not text that is not part of a data step.

[Emma2021]

Can you help with below:

When as below data:

data have;
input id date date9. flag;
format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1
2 1jul2021 1
2 6jul2021 .
;
run;


Then the wanted data should be as below:

1 4jul2021 1
1 10jul2021 2
2 1jul2021 1
2 6jul2021 .



But when the data have as below:

data have;
input id date date9. flag;
format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 4jul2022 1
1 10jul2022 4
1 16jul2022 2
1 18jul2022 1
2 1jul2021 1
2 6jul2021 .
;
run;



Then it should be as below:

1 1jul2021 .
1 4jul2022 1
2 1jul2021 1
2 6jul2021 .

[yabwon]

Check _all_ offered solutions. 🙂

B.

[Reeza]

data want;
set have;

by id;

*calculates the difference between previous date and current date;
date_diff = dif(date);

*If it's the first record with that ID then sets the difference to missing;
if first.id then date_diff = .;

*If the date difference is less than 5 (if less than or equal to change the sign) and not the first record and deletes it if required;
if date_diff < 5 and not first.id then delete;

run;

---

@Emma2021 wrote:
Have:
id date
1 1jul2021
1 1jul2021
1 4jul2021
1 10jul2021
1 16jul2021
1 18jul2021
2 3jul2021
2 15jul2021

Wanted:
id date
1 1jul2021
1 10jul2021
1 16jul2021
2 3jul2021
2 15jul2021

---

[Emma2021]

Can you help with below:

When as below data:

data have;
input id date date9. flag;
format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1
2 1jul2021 1
2 6jul2021 .
;
run;


Then the wanted data should be as below:

1 4jul2021 1
1 10jul2021 2
2 1jul2021 1
2 6jul2021 .



But when the data have as below:

data have;
input id date date9. flag;
format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 4jul2022 1
1 10jul2022 4
1 16jul2022 2
1 18jul2022 1
2 1jul2021 1
2 6jul2021 .
;
run;



Then it should be as below:

1 1jul2021 .
1 4jul2022 1
2 1jul2021 1
2 6jul2021 .

[Emma2021]

I have another twist--I have a "flag" variable. If not missing then get the row that associated with smallest date's row (see 1 4jul2021 1), but take if not missing and the flag variable is earliest date not with smallest flag value (see 1 16jul2021 2 although the 18jul2021 has smaller flag=1 ) . How can I do below? 

Have:
id date flag
1 1jul2021 .
1 1jul2021 .
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1

2 1jul2021 1

2 6jul2021 .

Wanted:
id date flag
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2

2 1jul2021 1

2 6jul2021 .

[PaigeMiller]

Again, I request data provided as SAS data step code, as I showed in my code, instead of simply text that is not a SAS data step.

[Emma2021]

data have;
input id date date9. flag;
format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1
2 1jul2021 1
2 6jul2021 .
;
run;

[yabwon]

Is it this what you need:

data have;
input id date date9. flag;
format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1
2 1jul2021 1
2 6jul2021 .
;
run;

data want;

_N_ = 0;
do until(last.id);
  set have;
  by id;

  _IORC_ + flag;

  if _IORC_ and (first.id or (date - _N_ >= 5)) then
    do;
      output;
      _N_ = date;
    end;
end;
_IORC_ = 0;
run;

proc print;
run;

?

Bart

[yabwon]

try this (I assume data are sorted):

data have;
input id date date9.;
format date date9.;
cards;
1 1jul2021
1 1jul2021
1 4jul2021
1 10jul2021
1 16jul2021
1 18jul2021
2 1jul2021
2 1jul2021
2 4jul2021
2 10jul2021
2 16jul2021
2 18jul2021
;
run;


data want;

do until(last.id);
  set have;
  by id;

  if first.id or (date - _N_ > 5) then
    do;
      output;
      _N_ = date;
    end;
end;

run;

proc print;
run;

Bart

[Emma2021]

I have another twist--I have a "flag" variable. If not missing then get the row that associated with smallest date's row (see 1 4jul2021 1), but take if not missing and the flag variable is earliest date not with smallest flag value (see 1 16jul2021 2 although the 18jul2021 has smaller flag=1 ) . How can I do below? 

Have:
id date flag
1 1jul2021 .
1 1jul2021 .
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1

2 1jul2021 1

2 6jul2021 .

Wanted:
id date flag
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2

2 1jul2021 1

2 6jul2021 .

[ballardw]

Adding "earliest" has now made it VERY important to actually duplicate your data. If your "date" is a character variable then "earliest" is not going to be what you think it is as comparisons with character values will not be in date order.

The way you describe use of the "flag" variable doesn't seem to make sense with the shown result. Such as why is the data with no flag value set included in the output?

---

@Emma2021 wrote:

I have another twist--I have a "flag" variable. If not missing then get the row that associated with smallest date's row (see 1 4jul2021 1), but take if not missing and the flag variable is earliest date not with smallest flag value (see 1 16jul2021 2 although the 18jul2021 has smaller flag=1 ) . How can I do below? 

Have:
id date flag
1 1jul2021 .
1 1jul2021 .
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1

2 1jul2021 1

2 6jul2021 .

Wanted:
id date flag
1 4jul2021 1
1 10jul2021 4
1 16jul2021 2

2 1jul2021 1

2 6jul2021 .

---