Desktop productivity for business analysts and programmers

Conditional Selection of Customer data by group

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Conditional Selection of Customer data by group

Hello all, 

I am doing data cleaning on a customer data. My goal is to select the 'survivor ' of the customer data based on data source, reliability and recency. To give an example I need to assign one survivor Identitynum to each Newid based on the information that Data source '0' is more reliable than '1', similarly Suspicious '0' is more reliable than '1', and the biggest ID is the most reliable than the others because it is the most recent. I need to write a code which will transform the table1 to the table2.

IDNewidIdentitynumData SourceSuspicious
111384900
139245711384410
125011891457211
991753210
3792791753210
1706203102903711
1733103745211
34609104444511

Table1

 

IDnewidIdentitynumData SourceSuspiciousSurvivor
1113849001
37927917532101
17062031029037111

Table2

 

Thank you!

 

PS:Assigning the survivor based on 'suspicious' and recency when max(ID) is not equal to the latest non-suspicious(suspicious=0)  ID in the same newid group is the main problem.


Accepted Solutions
Solution
‎07-19-2018 07:47 AM
Super User
Posts: 13,950

Re: Conditional Selection of Customer data by group


@Asli_A wrote:

Hello all, 

I am doing data cleaning on a customer data. My goal is to select the 'survivor ' of the customer data based on data source, reliability and recency. To give an example I need to assign one survivor Identitynum to each Newid based on the information that Data source '0' is more reliable than '1', similarly Suspicious '0' is more reliable than '1', and the biggest ID is the most reliable than the others because it is the most recent. I need to write a code which will transform the table1 to the table2.

ID Newid Identitynum Data Source Suspicious
1 1 13849 0 0
1392457 1 13844 1 0
1250118 9 14572 1 1
9 9 17532 1 0
37927 9 17532 1 0
1706203 10 29037 1 1
1733 10 37452 1 1
34609 10 44445 1 1

Table1

 

ID newid Identitynum Data Source Suspicious Survivor
1 1 13849 0 0 1
37927 9 17532 1 0 1
1706203 10 29037 1 1 1

Table2

 

Thank you!

 

PS:Assigning the survivor based on 'suspicious' and recency when max(ID) is not equal to the latest non-suspicious(suspicious=0)  ID in the same newid group is the main problem.


If I am understanding the question is basically: get the records into a desired order and select the correct one.

This might give you a start:

proc sort data=have;
  by newid datasource suspicious descending id;
run;

data want;
   set have;
   by newid;
   if first.newid;
run;

View solution in original post


All Replies
Solution
‎07-19-2018 07:47 AM
Super User
Posts: 13,950

Re: Conditional Selection of Customer data by group


@Asli_A wrote:

Hello all, 

I am doing data cleaning on a customer data. My goal is to select the 'survivor ' of the customer data based on data source, reliability and recency. To give an example I need to assign one survivor Identitynum to each Newid based on the information that Data source '0' is more reliable than '1', similarly Suspicious '0' is more reliable than '1', and the biggest ID is the most reliable than the others because it is the most recent. I need to write a code which will transform the table1 to the table2.

ID Newid Identitynum Data Source Suspicious
1 1 13849 0 0
1392457 1 13844 1 0
1250118 9 14572 1 1
9 9 17532 1 0
37927 9 17532 1 0
1706203 10 29037 1 1
1733 10 37452 1 1
34609 10 44445 1 1

Table1

 

ID newid Identitynum Data Source Suspicious Survivor
1 1 13849 0 0 1
37927 9 17532 1 0 1
1706203 10 29037 1 1 1

Table2

 

Thank you!

 

PS:Assigning the survivor based on 'suspicious' and recency when max(ID) is not equal to the latest non-suspicious(suspicious=0)  ID in the same newid group is the main problem.


If I am understanding the question is basically: get the records into a desired order and select the correct one.

This might give you a start:

proc sort data=have;
  by newid datasource suspicious descending id;
run;

data want;
   set have;
   by newid;
   if first.newid;
run;
New Contributor
Posts: 4

Re: Conditional Selection of Customer data by group

Yes! This was what i needed. Thank you Smiley Happy
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 77 views
  • 1 like
  • 2 in conversation