Help with first. and last.

[West26]

Hello,

Need help with below

DATE1         DATE2     DATE3
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jan-21  31-Jan-00
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jul-19   31-Dec-20
20-Apr-21   1-Jul-19   31-Dec-20
27-Apr-21   1-Jul-19   31-Dec-20
28-Apr-21   1-Jul-19   31-Dec-20
29-Apr-21   1-Jul-19   31-Dec-20

All the three dates were sorted
Scenario-1)
If DATE1 is not changed, DAT2 is not changed and only DATE3 has changed, I need those rows
That means first two rows
Scenario 2)
Suppose DATE3 doesn't exists
Now I need to get only first 4 rows of DATE1 and DATE2, as there is a change starting in DATE2, in row5

Thanks in advance!

[mkeintz]

1. Please provide your initial data in the form of a working data step.  Save us some time by doing this task.
   
   
2. Please provide your expected data as a result of the rule.  I ask this because you specify (bold italics mine) 
   

   Scenario-1)
   If DATE1 is not changed, DAT2 is not changed and only DATE3 has changed, I need those rows
   That means first two rows

   The first two rows are constant for all 3 dates, so how is it that you want the first two rows given the rule you state ("only DATE3 has changes)?
   
   Do you really mean you want all the rows preceding a change in date3?
   
   

[West26]

Thanks for your time and the suggestion. Appreciated.

data want;
input custid date1 :yymmdd10. date2 :yymmdd10. date3 :yymmdd10.
format date1 yymmdd10. date2 yymmdd10. date3 yymmdd10.;
datalines;
1 20/04/2021 01/01/2021 31/12/1999
2 20/04/2021 01/01/2021 31/12/1999
3 20/04/2021 01/01/2021 31/01/2000
4 20/04/2021 01/01/2021 31/12/1999
5 20/04/2021 01/07/2021 31/12/2000
6 20/04/2021 01/07/2021 31/12/2000
7 27/04/2021 01/07/2021 31/12/2000
8 28/04/2021 01/07/2021 31/12/2000
9 29/04/2021 01/07/2021 31/12/2000

;
run;

Scenario:
1) I want all the rows, preceding a change in date3, like below
Output:
1 20/04/2021 01/01/2021 31/12/1999
2 20/04/2021 01/01/2021 31/12/1999

2) Suppose DATE3 doesn't exists
I want all the rows, preceding a change in DATE2, like below
Output:
1 20/04/2021 01/01/2021
2 20/04/2021 01/01/2021
3 20/04/2021 01/01/2021
4 20/04/2021 01/01/2021

Thanks.

[Ksharp]

Scenario-1)

data have;
input (DATE1         DATE2     DATE3) (:date11.);
format date: date9.;
cards;
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jan-21  31-Jan-00
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jul-19   31-Dec-20
20-Apr-21   1-Jul-19   31-Dec-20
27-Apr-21   1-Jul-19   31-Dec-20
28-Apr-21   1-Jul-19   31-Dec-20
29-Apr-21   1-Jul-19   31-Dec-20
;
data temp;
  set have;
  by date1 date2 date3 notsorted;
  if first.date2 then group=0;
  group+first.date3;
run;
proc sql;
create table want as
select *
 from temp
  group by date1,date2
   having count(distinct group)>1 and group=1;
quit;

[West26]

Hi Ksharp,
I'm unable to see anything here.

[Ksharp · Accepted solution]

Scenario 2)

data have;
input (DATE1         DATE2     ) (:date11.);
format date: date9.;
cards;
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jan-21  31-Jan-00
20-Apr-21   1-Jan-21  31-Dec-99
20-Apr-21   1-Jul-19   31-Dec-20
20-Apr-21   1-Jul-19   31-Dec-20
27-Apr-21   1-Jul-19   31-Dec-20
28-Apr-21   1-Jul-19   31-Dec-20
29-Apr-21   1-Jul-19   31-Dec-20
;
data temp;
  set have;
  by date1 date2  notsorted;
  if first.date1 then group=0;
  group+first.date2;
run;
proc sql;
create table want as
select *
 from temp
  group by date1
   having count(distinct group)>1 and group=1;
quit;