BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
3 REPLIES 3
LinusH
Tourmaline | Level 20
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
deleted_user
Not applicable
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
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 737 views
  • 1 like
  • 2 in conversation