topic Re: Overlapping Periods - Select last row in SAS Programming

Overlapping Periods - Select last row

Jayaditya — Thu, 16 Sep 2021 13:59:37 GMT

Hi,

data work.testl;
infile datalines ;
input Policy_number:$8. Test_Rk:8. Version:8. Amt:8. from_dt:date. to_dt:date.;
datalines;
1 10 1 300 '01Jan2006'd '01Jan2034'd
1 10 1 700 '01Jan2007’d '01Jan2034'd

I have the above example. row 1 and row 2 are overlapping i.e. from_dt of the second row (01Jan2007) is less than to_dt of the first row(01Jan2034) of the same key policy_number,test_rk, and version. In that case, I want to select the second row in one dataset and write the first row in another table.

dataset 1 :

1 10 1 700 '01Jan2007’d '01Jan2034'd

dataset 2:

1 10 1 300 '01Jan2006'd '01Jan2034'd

Please help me with this.

Thanks,

Jayaditya

Re: Overlapping Periods - Select last row

ChrisNZ — Sat, 18 Sep 2021 02:23:03 GMT

Like this?

data TABLE1 TABLE2;
  set TEST1;
  if FROM_DT<lag(TO_DT) then output TABLE2;
  else TABLE1;
run;

Re: Overlapping Periods - Select last row

Jayaditya — Mon, 20 Sep 2021 11:25:04 GMT

Sorry, my explanation was not clear. I created a new dataline .

data have;
input IDl ID2 ID3 ID4 ID5 FROM_DT: date9. TO_DT:date9. Amount;
datalines;
10 12 100 1 4 31DEC2019 31DEC2021 100.00
10 12 100 1 4 31DEC2012 31DEC2019 100.00
10 12 101 4 4 31DEC2019 31DEC2021 200.00
10 12 101 4 4 31DEC2012 31DEC2019 200.00
10 12 103 6 4 31DEC2012 31DEC2019 300.00
10 12 103 6 4 31DEC2019 31DEC2021 300.00
10 12 104 7 4 31DEC2012 31DEC2019 400.00
10 12 104 7 4 31DEC2019 31DEC2021 400.00
11 13 105 3 6 01JUL2019 31DEC4747 500.00
11 13 105 3 6 01OCT2019 31DEC4747 500.00
11 14 106 4 4 010CT2019 31DEC4747 600.00
11 14 106 4 4 01JUL2019 31DEC4747 700.00

;

Here Key fields are ID1, ID2, ID3,Id4,ID5.

So for this combination:

1. if Amt were the same and no overlapping dates i.e. if I take the first two rows then take min(to_date) and max(from_date)

10 12 100 1 4 31DEC2019 31DEC2021 100.00
10 12 100 1 4 31DEC2012 31DEC2019 100.00

should become

10 12 100 1 4 31DEC2012 31DEC2021 100.00

2. if amt are same or different and if the dates are overlapping for ex: below rows

11 14 106 4 4 010CT2019 31DEC4747 600.00
11 14 106 4 4 01JUL2019 31DEC4747 700.00

I have to flag row 11 14 106 4 4 010CT2019 31DEC4747 600.00 as 1 and 11 14 106 4 4 01JUL2019 31DEC4747 700.00 as 0 as

they are overlapping dates so I have to flag the row with latest to_date to 1.

Please help me with this.

Regards,

Jay