How do i take the date value of the next row record and deduct by 1 day?

[StickyRoll]

How do i take the date value of the next row record and deduct by 1 day?

What I have and wish to achieve is this:

Update 2: to better potray the data that I have and what i want to achieve

Notice the red value is supposed to be taken from the 2nd row and 3rd row, where the value is actually -1 of 28-Dec2023 and -1 of 1-jan-2024.

How can i achieve this? It is possible to have more than 10 rows of such a record with unique "END_NUM" value for the same POLICE_ID and POLICE_YEAR.

data want;

POLICE_ID = A; POLICE_YEAR=2023; END_NUM = '0'; POLICE_EFF = '14Apr2023'd; POLICE_EXP='27Dec2023'd;

output;

POLICE_ID = A; POLICE_YEAR=2023; END_NUM = '1'; POLICE_EFF = '28Dec2023'd; POLICE_EXP='31Dec2023'd;

output;

POLICE_ID = A; POLICE_YEAR=2023; END_NUM = '2'; POLICE_EFF = '01Jan2024'd; POLICE_EXP='14Apr2024'd;

output;

run;

[PeterClemmensen]

"It is possible to have more than 10 rows of such a record with unique "END_NUM" value for the same POLICE_ID and POLICE_YEAR."

I don't understand this. Please provide sample data that resembles your request.

Why is POLICE_EXP = 14-APR-24 in the second obs ?

[Ksharp]

So what kind of data do you have ? Do you have variable 'POLICE_EXP'  or just want to generate it?

data have;
POLICE_ID = 'A'; POLICE_YEAR=2023; END_NUM = '0'; POLICE_EFF = '14Apr2023'd; POLICE_EXP='27Dec2023'd;
output;
POLICE_ID = 'A'; POLICE_YEAR=2023; END_NUM = '1'; POLICE_EFF = '28Dec2023'd; POLICE_EXP='14Apr2024'd;
format POLICE_EFF POLICE_EXP date9.;
output;
run;

data want;
merge have have(keep=POLICE_ID POLICE_YEAR POLICE_EFF 
      rename=(POLICE_ID=_POLICE_ID POLICE_YEAR=_POLICE_YEAR POLICE_EFF=_POLICE_EFF ) firstobs=2);
if POLICE_ID=_POLICE_ID and POLICE_YEAR=_POLICE_YEAR then want_POLICE_EXP=_POLICE_EFF-1;
format want_POLICE_EXP date9.;
drop _:;
run;

[StickyRoll]

@PeterClemmensen  @Ksharp I have updated my post by adding a new screenshot and revised my "data want" statement. 

[Ksharp]

data have;
POLICE_ID = 'A'; POLICE_YEAR=2023; END_NUM = '0'; POLICE_EFF = '14Apr2023'd; POLICE_EXP='27Dec2023'd;
output;
POLICE_ID = 'A'; POLICE_YEAR=2023; END_NUM = '1'; POLICE_EFF = '28Dec2023'd; POLICE_EXP='31Dec2023'd;
output;
POLICE_ID = 'A'; POLICE_YEAR=2023; END_NUM = '2'; POLICE_EFF = '01Jan2024'd; POLICE_EXP='14Apr2024'd;
output;
format POLICE_EFF POLICE_EXP date9.;
run;

data want;
merge have have(keep=POLICE_ID POLICE_YEAR POLICE_EFF 
      rename=(POLICE_ID=_POLICE_ID POLICE_YEAR=_POLICE_YEAR POLICE_EFF=_POLICE_EFF ) firstobs=2);
if POLICE_ID=_POLICE_ID and POLICE_YEAR=_POLICE_YEAR then POLICE_EXP=_POLICE_EFF-1;
drop _:;
run;

[StickyRoll]

Hi @Ksharp
In your sample code, you hardcode firstobs=2. May I know if it is possible to make it dynamic instead? In my dataset, there are more than 100k of different POLICE_ID and may be even more later.

[Ksharp]

"make it dynamic "
Yes. My code is dynamic as long as your data has been sorted by POLICE_ID and POLICE_YEAR.

[StickyRoll]

May i understand the purpose of having firstobs=2? I know it means the first observation should begin with row number 2 but what does it mean in this scenario?

[Kurt_Bremser]

---

@StickyRoll wrote:
May i understand the purpose of having firstobs=2? I know it means the first observation should begin with row number 2 but what does it mean in this scenario?

---

Since this particular read starts at the second observation, it always provides a "look-ahead" into the next observation.

[Ksharp]

Here is an example:

id   x
1   1
1   2
2   2
2   3
2  4

if using my code with firstobs=2 option would look forward an obs as Kurt pointed out.
----->
id  x  _id  _x  <--- is from the second dataset with firstobs=2
1  1    1    2
1  2    2    2
2   2   2    3
2   3   2    4
2   4   .    .