DATA Step, Macro, Functions and more

Identify correct record which satisfies criteria

Reply
Frequent Contributor
Posts: 75

Identify correct record which satisfies criteria

I have a dataset like this

Cust_noacct_noopen_dateenrollment
00112319-Jun-080
00145627-May-081
00178924-Oct-060
00110112-Jun-011

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

Super Contributor
Posts: 1,636

Identify correct record which satisfies criteria

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;

Frequent Contributor
Posts: 75

Re: Identify correct record which satisfies criteria

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

Super User
Posts: 19,768

Re: Identify correct record which satisfies criteria

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;

Frequent Contributor
Posts: 75

Re: Identify correct record which satisfies criteria

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...! Smiley Sad

Regards

Dhana

PROC Star
Posts: 7,467

Re: Identify correct record which satisfies criteria

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.

PROC Star
Posts: 7,467

Re: Identify correct record which satisfies criteria

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;

Super User
Posts: 10,018

Re: Identify correct record which satisfies criteria

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

Ask a Question
Discussion stats
  • 7 replies
  • 178 views
  • 0 likes
  • 5 in conversation