topic Re: Comparing two records on key values and applying filters in Developers

Comparing two records on key values and applying filters

Jagadeesh2907 — Wed, 29 Apr 2020 16:00:41 GMT

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

Re: Comparing two records on key values and applying filters

ChrisNZ — Wed, 29 Apr 2020 21:58:21 GMT

@Jagadeesh2907

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

Re: Comparing two records on key values and applying filters

Jagadeesh2907 — Thu, 30 Apr 2020 06:41:16 GMT

Thank you Chris.I will refer to your high performance SAS coding reference. Thank you for guiding me.