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;
@acemanhattan wrote:
This fixed the error.
Would you then do some sort of Proc Append loop to concatenate the tables?
Save the number of pairs (and therefore the number of datasets) to a macro variable:
data control;
input incurdt groupid :$10.;
call execute('%repeat_my_code(' !! put(_n_,best.) !! ',' !! put(incurdt,best.) !! ',' !! groupid !! ');');
call symput('endno',put(_n_,best.));
cards;
20161229 '10018623'
20170313 '10007253'
20170314 '10013882'
;
run;
Then use that in a short macro:
%macro concat(number);
%do i = 1 %to &number;
cust_type_lookup_&i.
%end;
%mend;
data cust_type_lookup;
set
%concat(&endno)
;
run;
Wrap your SQL into a macro and call that from a control dataset:
%macro repeat_my_code(seq,IncurDt,GroupID);
PROC SQL;
connect to oracle(authdomain="defaultauth" path=rra_trg_prd preserve_comments);
create table cust_type_lookup_&seq. 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 = &IncurDt.
and g.primy_grp_id_cd = "&GroupID."
)
;
QUIT;
%mend;
data control;
input incurdt groupid $;
call execute('%repeat_my_code(' !! put(_n_,best.) !! ',' !! put(incurdt,best.) !! ',' !! groupid !! ');');
cards;
20161229 10018623
20170313 10007253
20170314 10013882
;
run;
Once finished, you can concatenate the resulting datasets into one.
Thanks for the quick response, @Kurt_Bremser.
I'm getting the following error for each of the group IDs
ERROR: ORACLE prepare error: ORA-00904: "10018623": invalid identifier.
Does this have something to do with single vs double quotes?
It might be that Oracle is picky about single vs. double quotes when declaring string literals. I do not have experience with Oracle, only SAS.
Try this:
- change the where condition:
WHERE cdf.dwa_incurd_dt_key = &IncurDt.
and g.primy_grp_id_cd = &GroupID.
- and the control data step:
data control;
input incurdt groupid :$10.;
call execute('%repeat_my_code(' !! put(_n_,best.) !! ',' !! put(incurdt,best.) !! ',' !! groupid !! ');');
cards;
20161229 '10018623'
20170313 '10007253'
20170314 '10013882'
;
run;
This fixed the error.
Would you then do some sort of Proc Append loop to concatenate the tables?
@acemanhattan wrote:
This fixed the error.
Would you then do some sort of Proc Append loop to concatenate the tables?
Save the number of pairs (and therefore the number of datasets) to a macro variable:
data control;
input incurdt groupid :$10.;
call execute('%repeat_my_code(' !! put(_n_,best.) !! ',' !! put(incurdt,best.) !! ',' !! groupid !! ');');
call symput('endno',put(_n_,best.));
cards;
20161229 '10018623'
20170313 '10007253'
20170314 '10013882'
;
run;
Then use that in a short macro:
%macro concat(number);
%do i = 1 %to &number;
cust_type_lookup_&i.
%end;
%mend;
data cust_type_lookup;
set
%concat(&endno)
;
run;
I agree with Kurt Bremser about rotating your horizontal lists to vertical (data set rows).
I wonder if you can extract your pairs into a data set with proc sql?
Art Carpenter and I wrote a paper about List Processing, a while back.
http://www.sascommunity.org/wiki/List_Processing_Basics_Creating_and_Using_Lists_of_Macro_Variables
Here is a generic tool to read a list and call a macro
http://www.sascommunity.org/wiki/Macro_CallMacr
Ron Fehd
It might be an option to write your input values for IncurDt and GroupID to a SAS dataset, and then join that table on in your query.
My first thought was it sounds like you want to build a WHERE clause like:
where
(cdf.dwa_incurd_dt_key in (20161229) and g.primy_grp_id_cd in('10018623') )
or (cdf.dwa_incurd_dt_key in (20170313) and g.primy_grp_id_cd in('10007253') )
or (cdf.dwa_incurd_dt_key in (20170314) and g.primy_grp_id_cd in('10013882') )
;
Is that right? If so, you can write a little macro that would generate that clause from the two lists, e.g.:
%let IncurDt = 20161229 20170313 20170314;
%let GroupID = '10018623' '10007253' '10013882';
%macro mkWhere(IncurDt=,GroupID=);
%local i whereCl;
%do i=1 %to %sysfunc(countw(&IncurDt));
%if &i>1 %then %let WhereCl=&WhereCl or ;
%let WhereCl=&WhereCl (cdf.dwa_incurd_dt_key in (%scan(&IncurDt,&i)) and g.primy_grp_id_cd in(%scan(&GroupID,&i)) ) ;
%end;
&WhereCl
%mend mkWhere;
Test Like:
126 %put %mkWhere(IncurDt=&IncurDt,GroupID=&GroupID) ; (cdf.dwa_incurd_dt_key in (20161229) and g.primy_grp_id_cd in('10018623') ) or (cdf.dwa_incurd_dt_key in (20170313) and g.primy_grp_id_cd in('10007253') ) or (cdf.dwa_incurd_dt_key in (20170314) and g.primy_grp_id_cd in('10013882') )
Call like:
proc sql;
select ...
from ...
where
%mkWhere(IncurDt=&IncurDt,GroupID=&GroupID)
;
quit
Note I removed the commas from the lists, just to avoid the need for macro quoting.
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.