Hi,
Please refer the following input and output.
data have;
input CUSTOMER_ID policy $2. date anydtdte23.;
format date date9.;
cards;
1057086 a 21AUG2014
1057086 b 25AUG2014
1057086 c 17SEP2014
1057086 d 17SEP2014
1057086 e 19SEP2014
1057086 f 26SEP2014
1057086 g 26SEP2014
1057086 h 26Oct2015
1057086 i 21Dec2016
1020006 j 22Nov2011
1020006 k 21Dec2012
1020006 l 30Jan2015
1020006 m 30Jan2015
1020006 o n 30Jan2015
1020006 p 30Jan2015
1020006 q 30Jan2015
1020006 r 30Jan2015
1020006 s 31Jan2018
;
run;
Output Want in following format.
CUSTOMER_ID policy date Count
1020006 o . 0
1020006 j 22-Nov-11 1
1020006 k 21-Dec-12 2
1020006 l 30-Jan-15 3
1020006 m 30-Jan-15 3
1020006 p 30-Jan-15 3
1020006 q 30-Jan-15 3
1020006 r 30-Jan-15 3
1020006 s 31-Jan-18 8
1057086 a 21-Aug-14 0
1057086 b 25-Aug-14 1
1057086 c 17-Sep-14 2
1057086 d 17-Sep-14 2
1057086 e 19-Sep-14 4
1057086 f 26-Sep-14 5
1057086 g 26-Sep-14 5
1057086 h 26-Oct-15 7
1057086 i 21-Dec-16 8
Sort and use some less-than-simple by-group processing:
data have;
input CUSTOMER_ID policy $2. date anydtdte23.;
format date date9.;
cards;
1057086 a 21AUG2014
1057086 b 25AUG2014
1057086 c 17SEP2014
1057086 d 17SEP2014
1057086 e 19SEP2014
1057086 f 26SEP2014
1057086 g 26SEP2014
1057086 h 26Oct2015
1057086 i 21Dec2016
1020006 j 22Nov2011
1020006 k 21Dec2012
1020006 l 30Jan2015
1020006 m 30Jan2015
1020006 o n 30Jan2015
1020006 p 30Jan2015
1020006 q 30Jan2015
1020006 r 30Jan2015
1020006 s 31Jan2018
;
run;
proc sort data=have;
by customer_id date;
run;
data want;
set have;
by customer_id date;
retain count sec_count;
if first.customer_id
then do;
count = 0;
sec_count = 1;
end;
else if first.date
then do;
count + sec_count;
sec_count = 1;
end;
else sec_count + 1;
drop sec_count;
run;
proc print data=want noobs;
run;
Result:
CUSTOMER_ ID policy date count 1020006 o . 0 1020006 j 22NOV2011 1 1020006 k 21DEC2012 2 1020006 l 30JAN2015 3 1020006 m 30JAN2015 3 1020006 p 30JAN2015 3 1020006 q 30JAN2015 3 1020006 r 30JAN2015 3 1020006 s 31JAN2018 8 1057086 a 21AUG2014 0 1057086 b 25AUG2014 1 1057086 c 17SEP2014 2 1057086 d 17SEP2014 2 1057086 e 19SEP2014 4 1057086 f 26SEP2014 5 1057086 g 26SEP2014 5 1057086 h 26OCT2015 7 1057086 i 21DEC2016 8
Thanks KurtBremser.
Its working.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: