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!
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;
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?
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.