Identify correct record which satisfies criteria

[dhana]

I have a dataset like this

Cust_no	acct_no	open_date	enrollment
001	123	19-Jun-08	0
001	456	27-May-08	1
001	789	24-Oct-06	0
001	101	12-Jun-01	1

The customer need to enroll their account number in the reward program, so that they will get some discount or future benefits.

A customer can have any number of accounts and he can enroll any number of account in the program.

In the above example the customer has 4 accounts and he has enrolled two of his account in the program.

But for the internal processing we will consider only one account which was enrolled & opened very recently should be considered.

From the above example how can we take out only the second record which satisfies the above critieria.

Cust 2 has enrolled in the program and with account number 456 being the one which opened recently.

When I sort the dataset by descending open_date I am getting the most recently opened account, but it is not always the most recently opened account is enrolled in the program.

Thanks

Dhana

[Linlin]

data have;

informat open_date date9.;

format open_date date9.;

input Cust_no $ acct_no $ open_date enrollment;

cards;

001 123 19-Jun-08 0

001 456 27-May-08 1

001 789 24-Oct-06 0

001 101 12-Jun-01 1

002 123 19-Jun-08 0

002 456 27-May-08 1

002 789 24-Oct-06 0

002 101 12-Jun-01 1

;

proc sort data=have out=temp;

by cust_no open_date;

where enrollment=1;

data want;

  set temp;

  by cust_no;

  if last.cust_no;

run;

proc print;run;

[dhana]

Thank you Linlin...! After looking at your code, I realised that I have missed a condition in the question.

Here is that... If a customer has multiple accounts and registered multiple account in the program then most recently opened and enrolled account (only one) will be considered.

and also we need to consider the customer who has account but not enrolled in the program.

001 123 19-Jun-08 0

001 456 27-May-08 1

001 789 24-Oct-06 0

001 101 12-Jun-01 1

002 123 19-Jun-08 0

002 456 27-May-08 1

002 789 24-Oct-06 0

002 101 12-Jun-01 1

003 345 16-Oct-04 0

In the above data I have included one more row where the customer has an account but he didnt enroll it in the program.

The output dataset should look like this

001 456 27-May-08 1

002 456 27-May-08 1

003 345 16-Oct-04 0

How do I tackle this also...

Regards

Dhana

[Reeza]

Add enrollment into your sort option as well.

data have;

informat open_date date9.;

format open_date date9.;

input Cust_no $ acct_no $ open_date enrollment;

cards;

001 123 19-Jun-08 0

001 456 27-May-08 1

001 789 24-Oct-06 0

001 101 12-Jun-01 1

002 123 19-Jun-08 0

002 456 27-May-08 1

002 789 24-Oct-06 0

002 101 12-Jun-01 1

003 345 16-Oct-04 0

;

run;

proc sort data=have;

by cust_no descending enrollment descending open_date;

run;

data want;

set have;

by cust_no descending enrollment descending open_date;

if first.cust_no;

run;

[dhana]

Thank You Reeza...! I am really very tired and exhausted with the the work load. So I am not able to think properly...!

Please apologize for asking easy to solve questions...!

Regards

Dhana

[art297]

Danna, I can't speak for Reeza but, IMHO there are NO questions that are easy to solve by everyone!  What may be easy for some of us are not only impossible for others, but often are even difficult to discover via web searches.

[art297]

Just a slight modification to Linlin's original code, namely regarding the sort:

data have;

  informat open_date date9.;

  format open_date date9.;

  input Cust_no $ acct_no $ open_date enrollment;

cards;

001 123 19-Jun-08 0

001 456 27-May-08 1

001 789 24-Oct-06 0

001 101 12-Jun-01 1

002 123 19-Jun-08 0

002 456 27-May-08 1

002 789 24-Oct-06 0

002 101 12-Jun-01 1

003 345 16-Oct-04 0

;

proc sort data=have out=temp;

  by cust_no enrollment open_date;

run;

data want;

  set temp;

  by cust_no;

  if last.cust_no;

run;

[Ksharp]

How about:

data have;
  informat open_date date9.;
  format open_date date9.;
  input Cust_no $ acct_no $ open_date enrollment;
cards;
001 123 19-Jun-08 0
001 456 27-May-08 1
001 789 24-Oct-06 0
001 101 12-Jun-10 1
002 123 19-Jun-08 0
002 456 27-May-08 1
002 789 24-Oct-06 0
002 101 12-Jun-01 1
003 345 16-Oct-04 0
003 325 16-Apr-04 0
003 245 16-Jan-04 0
;
run;

data want(drop=count);
 set have;
 by cust_no notsorted;
 retain _nobs;
 if first.cust_no then do;
                         count=0;_nobs=_n_;
                       end;
 if enrollment eq 1 then do;
                count+1;
                if count eq 1 then output;
                  end;
 if last.cust_no and count eq 0 then do; 
                set have point=_nobs; output;
                  end;
run;

Ksharp