As @Tom pointed out your response to my question is contradictory. Here is a solution that should word if there are no duplicate key-pairs in table C or table L. It doesn't matter if there are duplicates in table M:
data need (keep=CNCPT_NM TRRTRY_SHRT_NM APPDWLD_CUST_PER_CY APPACTV_CUST_PER_CY
APPDWLD_CUST_PER_LY APPACTV_CUST_PER_LY APPDWLD_CUST_PP_CHNG APPACTV_CUST_PP_CHNG) /view=need;
merge
SUMM1_REV_TREE ( keep=CNCPT_NM TRRTRY_SHRT_NM CUST_CY CUST_LY) /* M */
APP_DWLD_ACTV_CUST_CY (obs=0 keep=CNCPT_NM TRRTRY_SHRT_NM APPDWLD_CUST APPACTV_CUST) /* C */
APP_DWLD_ACTV_CUST_LY (obs=0 keep=CNCPT_NM TRRTRY_SHRT_NM APPDWLD_CUST APPACTV_CUST) /* L */
;
if _n_=1 then do;
declare hash C (dataset:'APP_DWLD_ACTV_CUST_CY (keep=CNCPT_NM TRRTRY_SHRT_NM APPDWLD_CUST APPACTV_CUST)');
c.definekey('CNCPT_NM','TRRTRY_SHRT_NM');
c.definedata('APPDWLD_CUST','APPACTV_CUST');
c.definedone();
declare hash L (dataset:'APP_DWLD_ACTV_CUST_LY (keep=CNCPT_NM TRRTRY_SHRT_NM APPDWLD_CUST APPACTV_CUST)');
l.definekey('CNCPT_NM','TRRTRY_SHRT_NM');
l.definedata('APPDWLD_CUST','APPACTV_CUST');
l.definedone();
end;
if C.find() = 0 then do; /* Retrieve C.APPDWLD_CUST and APPACTV_CUST */
APPDWLD_CUST_PER_CY = APPDWLD_CUST/CUST_CY ;
APPACTV_CUST_PER_CY = APPACTV_CUST/CUST_CY;
end;
if L.find() = 0 then do; /* Retrieve L.APPDWLD_CUST and L.APPACTV_CUST*/
APPDWLD_CUST_PER_LY= APPDWLD_CUST/CUST_LY ;
APPACTV_CUST_PER_LY= APPACTV_CUST/CUST_LY ;
end;
format APPDWLD_CUST_PER_CY APPACTV_CUST_PER_CY
APPDWLD_CUST_PER_LY APPACTV_CUST_PER_LY percent8.2;
APPDWLD_CUST_PP_CHNG=APPDWLD_CUST_PER_CY - APPDWLD_CUST_PER_LY;
APPACTV_CUST_PP_CHNG=APPACTV_CUST_PER_CY - APPACTV_CUST_PER_LY;
run;
proc sort data=need out=SUMM7_DIGITAL_CUST0 ;
by CNCPT_NM TRRTRY_SHRT_NM;
run;
The MERGE statement applies OBS=0 to tables C and L. That is done because merge will reset all the exclusively C-table and L-table variables to missing at the start of each iteration of the DATA step. You need that to protect against situations in which the current iteration finds no matching table C entry, which would leave C-table values retrieved from the most recent successful c.find() retrieval (same for L of course). Of course, if every entry in table M has a match in tables L and C, then the defense against such retained values is not needed.
I don't guarantee this code, in the absence of usable sample data.
This code would need modification if C or L have duplicate key pairs.
... View more