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.
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;
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).
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
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;
You guys are unbelievably helpful. You guys make our worklife so easy.. thank you from the bottom of my heart
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.