Hi I have a customer linking dataset and i have five attibutes by below example:
APPLICATION_NUM LINK_CUSTOMER_ID TEMP_CUSTOMER_ID LINKED_CUSTOMER_ID LINK_TYPE
11661523 9086964 9572284 6543652 160
11661523 9086964 9572284 9572284 190
11661523 9572284 9572284 6543652 160
what is want to do is to identify records with same application_num and link_customer_id and when they are same, i want to output only the record when the temp_customer_id is equal to linked_customer_id.
that is for this example: i would need the output to be as below.
APPLICATION_NUM LINK_CUSTOMER_ID TEMP_CUSTOMER_ID LINKED_CUSTOMER_ID LINK_TYPE
11661523 9086964 9572284 9572284 190
11661523 9572284 9572284 6543652 160
You know you need to provide the data as a data step (that you have run and vetted *from the code pasted*).
Please do so.
This should give you a starting point:
data HAVE;
input APPLICATION_NUM LINK_CUSTOMER_ID TEMP_CUSTOMER_ID LINKED_CUSTOMER_ID LINK_TYPE ;
cards;
11661523 9086964 9572284 6543652 160
11661523 9086964 9572284 9572284 190
11661523 9572284 9572284 6543652 160
;
proc sql;
select *
, TEMP_CUSTOMER_ID=LINKED_CUSTOMER_ID as FLAG
, count(*) as DUP
from HAVE
group by APPLICATION_NUM, LINK_CUSTOMER_ID
having DUP=1 | FLAG=1;
APPLICATION_NUM | LINK_CUSTOMER_ID | TEMP_CUSTOMER_ID | LINKED_CUSTOMER_ID | LINK_TYPE | FLAG | DUP |
---|---|---|---|---|---|---|
11661523 | 9086964 | 9572284 | 9572284 | 190 | 1 | 2 |
11661523 | 9572284 | 9572284 | 6543652 | 160 | 0 | 1 |
You know you need to provide the data as a data step (that you have run and vetted *from the code pasted*).
Please do so.
This should give you a starting point:
data HAVE;
input APPLICATION_NUM LINK_CUSTOMER_ID TEMP_CUSTOMER_ID LINKED_CUSTOMER_ID LINK_TYPE ;
cards;
11661523 9086964 9572284 6543652 160
11661523 9086964 9572284 9572284 190
11661523 9572284 9572284 6543652 160
;
proc sql;
select *
, TEMP_CUSTOMER_ID=LINKED_CUSTOMER_ID as FLAG
, count(*) as DUP
from HAVE
group by APPLICATION_NUM, LINK_CUSTOMER_ID
having DUP=1 | FLAG=1;
APPLICATION_NUM | LINK_CUSTOMER_ID | TEMP_CUSTOMER_ID | LINKED_CUSTOMER_ID | LINK_TYPE | FLAG | DUP |
---|---|---|---|---|---|---|
11661523 | 9086964 | 9572284 | 9572284 | 190 | 1 | 2 |
11661523 | 9572284 | 9572284 | 6543652 | 160 | 0 | 1 |
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.