topic Re: LAG function help in SAS Programming

LAG function help

Emma2021 — Tue, 04 Jan 2022 13:56:28 GMT

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!

Re: LAG function help

PaigeMiller — Tue, 04 Jan 2022 14:25:37 GMT

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.

Re: LAG function help

yabwon — Tue, 04 Jan 2022 14:34:24 GMT

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

Re: LAG function help

Emma2021 — Tue, 04 Jan 2022 19:17:27 GMT

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 .

Re: LAG function help

PaigeMiller — Tue, 04 Jan 2022 14:45:15 GMT

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.

Re: LAG function help

yabwon — Tue, 04 Jan 2022 15:00:34 GMT

Check _all_ offered solutions. 🙂

Re: LAG function help

Reeza — Tue, 04 Jan 2022 16:46:46 GMT

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

Re: LAG function help

Emma2021 — Tue, 04 Jan 2022 18:35:09 GMT

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 .

Re: LAG function help

Emma2021 — Tue, 04 Jan 2022 19:17:44 GMT

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 .

Re: LAG function help

PaigeMiller — Tue, 04 Jan 2022 19:20:46 GMT

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.

Re: LAG function help

Emma2021 — Tue, 04 Jan 2022 19:41:03 GMT

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;

Re: LAG function help

ballardw — Tue, 04 Jan 2022 19:45:44 GMT

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 .

Re: LAG function help

yabwon — Tue, 04 Jan 2022 20:14:56 GMT

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

Re: LAG function help

Emma2021 — Tue, 04 Jan 2022 21:28:03 GMT

Thank you, but it is wrong because the 5 is date difference (LAG) should be used not _n_.

Re: LAG function help

Emma2021 — Tue, 04 Jan 2022 21:29:23 GMT

Not necessary earliest because the "flag" variable is important as well.

Re: LAG function help

ballardw — Tue, 04 Jan 2022 21:41:29 GMT

@Emma2021 wrote:

Not necessary earliest because the "flag" variable is important as well.

I have to interpret your statement " If not missing then get the row that associated with smallest date's row " as referring to the Flag variable. You do not provide any example/description or use for what to do with a missing flag variable value.

Since none of the Subjid=1 with missing Flag values were included why is the Subjid=2 with a missing flag value included in the output.

You really have not very clearly described the exact roll the value of the Flag variable plays. Providing example output without clear rules is an incomplete description. I can provide programming that would create the exact example that you show for the given input but it very likely would not work in a general sense because the the actual process or rules have not been provided that indicate why the record with

2 6jul2021 .

is wanted in the output but the records like

1 1jul2021 .

are not wanted.

Re: LAG function help

yabwon — Tue, 04 Jan 2022 21:45:39 GMT

Why is it wrong?

Re: LAG function help

Emma2021 — Wed, 05 Jan 2022 00:45:27 GMT

Combination of date and flag should be used:
1. Check 5 days lag
2. If lag is within 5 days then take the first row:
But here can be flag missing or not missing,
If all missing, then just take the first date
If some not missing, then take the not missing first flag
If all not missing flag, then take the first date

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;

Wanted should be as below:

1 4jul2021 1

1 10jul2021 2

2 1jul2021 1
2 6jul2021 .

But when as below data:

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 .

Re: LAG function help

Emma2021 — Tue, 04 Jan 2022 23:32:49 GMT

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 .

Re: LAG function help

Emma2021 — Tue, 04 Jan 2022 23:33:35 GMT