SQL JOINS TO HASH OBJECT WITH MULTIPLE CONDITIONS

[Bharat_P]

proc sql;
  create table temp as
    select A.CNCPT_NM
         , A.TRRTRY_SHRT_NM
         , A.LMG_MEM_CARD_NUMBER
         , case when missing(B.REVENUE) then 'CHURN' end as FLG
    from PREV_CUST_LST A
      left join CURR_CUST_LST B on A.CNCPT_NM=B.CNCPT_NM and A.TRRTRY_SHRT_NM=B.TRRTRY_SHRT_NM and A.LMG_MEM_CARD_NUMBER=B.LMG_MEM_CARD_NUMBER
    ;

  create table CHURN_CUST as
    select CNCPT_NM
         , TRRTRY_SHRT_NM
         , count(case when FLG='CHURN' then LMG_MEM_CARD_NUMBER end) as CHURN_CUST
    from temp
    group by 1,2
    ;
  quit;

I have the basic idea of how hash coding works but struggling to resolve this with the case when missing part.

[Kurt_Bremser]

This should look like

data temp;
set prev_cust_lst;
if _n_ = 1
then do;
  declare hash curr (dataset:"curr_cust_lst (keep=cncpt_nm trrtry_shrt_nm lmg_mem_card_number)");
  curr.definekey('cncpt_nm','trrtry_shrt_nm','lmg_mem_card_number');
  curr.definedone();
end;
if curr.check() ne 0 then flg = 'CHURN';
run;

For tested code, supply example data in usable form (data step with datalines).

[JeanDo]

Hello,

You should try this code:

proc sql;
  create table temp as
    select A.CNCPT_NM
         , A.TRRTRY_SHRT_NM
         , A.LMG_MEM_CARD_NUMBER
         , case when missing(B.REVENUE) then 'CHURN' end as FLG
    from PREV_CUST_LST A
      left join CURR_CUST_LST B on A.CNCPT_NM=B.CNCPT_NM and A.TRRTRY_SHRT_NM=B.TRRTRY_SHRT_NM and A.LMG_MEM_CARD_NUMBER=B.LMG_MEM_CARD_NUMBER
    ;

  create table CHURN_CUST as
    select CNCPT_NM
         , TRRTRY_SHRT_NM
         , sum(ifn(FLG='CHURN',1,0)) as CHURN_CUST
    from temp
    group by 1,2
    ;
  quit;

Regards,

JD

[Kurt_Bremser · Accepted solution]

And the whole operation should look like this:

proc sort data=prev_cust_lst;
by cncpt_nm trrtry_shrt_nm;
run;

data churn_cust (keep=cncpt_nm trrtry_shrt_nm churn_cust);
set prev_cust_lst;
by cncpt_nm trrtry_shrt_nm;
if _n_ = 1
then do;
  declare hash curr (dataset:"curr_cust_lst (keep=cncpt_nm trrtry_shrt_nm lmg_mem_card_number)");
  curr.definekey('cncpt_nm','trrtry_shrt_nm','lmg_mem_card_number');
  curr.definedone();
end;
if first.trrtry_shrt_nm then churn_cust = 0;
if curr.check() ne 0 then churn_cust + 1;
if last.trrtry_shrt_nm;
run;

[Bharat_P]

You guys are unbelievably helpful. You guys make our worklife so easy.. thank you from the bottom of my heart