Desktop productivity for business analysts and programmers

SAS code: count continuous records

Reply
N/A
Posts: 0

SAS code: count continuous records

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!
Esteemed Advisor
Posts: 5,198

Re: SAS code: count continuous records

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
Data never sleeps
N/A
Posts: 0

Re: SAS code: count continuous records

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 .?
Esteemed Advisor
Posts: 5,198

Re: SAS code: count continuous records

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
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 148 views
  • 1 like
  • 2 in conversation