I have some code that, generally, takes as inputs a date and subscriber number and maps that pairing to a known line of business. I need to be able to enter a list of dates and a list of subscriber numbers and have the code be smart enough to only search by ordered pairs.
Currently, if I enter my inputs as
%LET IncurDt = 20161229,20170313,20170314;
%LET GroupID = '10018623','10007253','10013882';
the code will execute, but it looks for each combination of IncurDt & GroupID, which not only creates unneeded observations, but can take hours to execute if I need to enter, say, 20 dates and 20 IDs. I need the code to understand that I want to search in pairs where the pairs are the first element from each list, or the second element from each list, or the third element from each list.
I'll paste the whole code below, but I think the other relevant part of the code is this one (which comes at the end of the code).
WHERE cdf.dwa_incurd_dt_key in (&IncurDt.)
and g.primy_grp_id_cd in (&GroupID.)
Whole code
/************************************************************************************************/
/*For when the LOB for a large pended claim is “UNK” and you want to know which LOB it really is*/
/************************************************************************************************/
/************ INPUTS ************/
%LET IncurDt = 20161229,20170313,20170314;
%LET GroupID = '10018623','10007253','10013882';
/********** END INPUTS **********/
/*This macro enables the use of each state's LOB_CUST_TYPE macro. It searches through each of the lob_cust_type macros and replaces
1. pre. with rre. since we are using the rre. join
2. bsre. with rre. since we are using the rre. join
3. i.dwa_incurd_in_force_mo_key with floor(cdf.dwa_incurd_dt_key) since we are using cdf*/
%Macro set(STATE=);
%LET code_dir2 = /sasapps/EarningsAnalysis/&STATE.;
%INCLUDE "&code_dir2/step_00_lob_cust_type.sas";
%GLOBAL CUST_TYPE_&STATE.;
%LET CUST_TYPE_&STATE. =
%SysFunc(TranWrd(
%SysFunc(TranWrd(
%SysFunc(TranWrd( %lob_cust_type , %Str(pre.) , %Str(rre.) ) )
, %Str(bsre.) , %Str(rre.) ) )
, %Str(i.dwa_incurd_in_force_mo_key) , %Str(floor(cdf.dwa_incurd_dt_key / 100)) ) );
%mend set;
%set(STATE=WA);
%set(STATE=OR);
%set(STATE=ID);
%set(STATE=UT);
/*Use to look up lob cust type to break out INDD/LCU1/UNK for large pended claims tab*/
PROC SQL;
connect to oracle(authdomain="defaultauth" path=rra_trg_prd preserve_comments);
create table cust_type_lookup as select * from connection to oracle(
SELECT DISTINCT
c.clnt_st_cd
, cdf.dwa_incurd_dt_key
, cdf.dwa_incurd_dt_key
, g.primy_grp_id_cd
, g.grp_name
, g.parnt_grp_id_cd
, case
when c.clnt_st_cd = 'WA' then &CUST_TYPE_WA.
when c.clnt_st_cd = 'OR' then &CUST_TYPE_OR.
when c.clnt_st_cd = 'ID' then &CUST_TYPE_ID.
when c.clnt_st_cd = 'UT' then &CUST_TYPE_UT.
else 'NA' end as LOB_CUST_TYPE
, rre.trnsl_cstmr_typ_cd
FROM dwa_vw.clm_detl_fact cdf
join dwa_vw.cli_catz ccz
on cdf.dwa_cli_catz_key = ccz.dwa_cli_catz_key
join dwa_vw.grp_strct g
on cdf.dwa_grp_strct_key = g.dwa_grp_strct_key
join dwa_vw.clnt_st c
on cdf.dwa_clnt_st_key = c.dwa_clnt_st_key
join dwa_vw.mbr m
on cdf.dwa_mbr_key = m.dwa_mbr_key
join dwa_vw.grp_cls_pln gcp
on cdf.dwa_grp_cls_pln_key = gcp.dwa_grp_cls_pln_key
left join
(select
ref.dwa_rptg_elem_fact_key,
ref.dwa_grp_cls_pln_key,
ref.dwa_prod_cat_key,
ref.dwa_rec_eff_dt_key,
ref.dwa_rec_cncl_dt_key,
bsre.trnsl_cstmr_typ_cd,
bsre.trnsl_fincl_arngmt_cd,
pre.trnsl_gl_co_chk_stock_id_cd,
pre.trnsl_prod_typ_cd,
pre.trnsl_ntwk_cd
from
dwa_vw.rptg_elem_fact ref
left join dwa_vw.bus_sgmt_rptg_elem bsre
on ref.dwa_bus_sgmt_rptg_elem_key = bsre.dwa_bus_sgmt_rptg_elem_key
left join dwa_vw.prod_rptg_elem pre
on ref.dwa_prod_rptg_elem_key = pre.dwa_prod_rptg_elem_key) rre
on cdf.dwa_grp_cls_pln_key = rre.dwa_grp_cls_pln_key
and cdf.dwa_prod_cat_key = rre.dwa_prod_cat_key
and cdf.dwa_incurd_dt_key between rre.dwa_rec_eff_dt_key and rre.dwa_rec_cncl_dt_key
WHERE cdf.dwa_incurd_dt_key in (&IncurDt.)
and g.primy_grp_id_cd in (&GroupID.)
)
;
QUIT;
... View more
