topic Re: How to compare row to row value in SAS Programming

How to compare row to row value

Shantaram — Wed, 17 Oct 2018 10:58:57 GMT

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

Re: How to compare row to row value

Kurt_Bremser — Wed, 17 Oct 2018 11:18:42 GMT

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

Re: How to compare row to row value

Shantaram — Thu, 18 Oct 2018 05:24:34 GMT

Thanks KurtBremser.

Its working.