topic Re: Help with first. and last. in SAS Programming

Help with first. and last.

West26 — Tue, 04 May 2021 00:34:13 GMT

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!

Re: Help with first. and last.

mkeintz — Tue, 04 May 2021 02:37:23 GMT

Please provide your initial data in the form of a working data step. Save us some time by doing this task.
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?

Re: Help with first. and last.

Ksharp — Tue, 04 May 2021 10:14:43 GMT

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;

Re: Help with first. and last.

West26 — Tue, 04 May 2021 10:11:06 GMT

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.

Re: Help with first. and last.

West26 — Tue, 04 May 2021 10:13:01 GMT

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

Re: Help with first. and last.

Ksharp — Tue, 04 May 2021 10:18:43 GMT

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;