topic Re: SAS code: count continuous records in SAS Enterprise Guide

SAS code: count continuous records

deleted_user — Fri, 06 Feb 2009 07:02:21 GMT

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!

Re: SAS code: count continuous records

LinusH — Fri, 06 Feb 2009 07:33:48 GMT

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

Re: SAS code: count continuous records

deleted_user — Fri, 06 Feb 2009 08:15:28 GMT

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 .?

Re: SAS code: count continuous records

LinusH — Fri, 06 Feb 2009 08:34:05 GMT

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