SAS code: count continuous records

[deleted_user]

My dataset like this:

CusID Date
123 31/5/2007
123 31/7/2007
123 31/8/2007
123 30/9/2007
XYZ 31/7/2007
XYZ 31/1//2008
XYZ 30/4/2008

How can I find out which customer have 2 or more continuous month-end records?

Thanks!

[LinusH]

You can use SET BY together with first.CustId to keep track on one customers records (so you don't compare dates between different customers).

You can use RETAIN statement or the LAG() function to hold/retrieve values between observations.

The INTNX function lets you move your date monthly (among a lot of other valid intervals), which let you compare the dates between to observations.

/Linus

[deleted_user]

My code like this:

Data Temp8;
set temp7;
by cusid date;
if first.date=1 then do;
Diff=intck('month', lag(timekey),timekey);
if last.date =1 then output;
end;
run;


Result:

CusID Date Diff
123 31/5/2007 .
123 31/7/2007 2
123 31/8/2007 1
123 30/9/2007 1
XYZ 31/7/2007 -2
XYZ 31/1//2008 6
XYZ 30/4/2008 3

how to compare the same cusid and why there is a .?

[LinusH]

You don't need to do BY date, since date seems to unique, and you want to keep track on different CustId.
Try to change to:

if not first.CustId then do;
Diff=intck('month', lag(timekey),timekey);
if diff eq 1 then output;

With this you have multiple records per custId in your output.
The . means missing. You tried to compare a value from observation 1 minus 1, and that observation does not exist...

Regards,
Linus