topic Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists in SAS Programming

Calling 2 Macro Variables in Ordered Pairs from 2 Lists

acemanhattan — Mon, 21 Aug 2017 12:35:14 GMT

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;

Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists

Kurt_Bremser — Mon, 21 Aug 2017 12:48:09 GMT

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.

Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists

acemanhattan — Mon, 21 Aug 2017 13:04:07 GMT

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?

Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists

Quentin — Mon, 21 Aug 2017 13:20:09 GMT

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.

Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists

Kurt_Bremser — Mon, 21 Aug 2017 13:29:18 GMT

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;

Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists

acemanhattan — Mon, 21 Aug 2017 13:46:59 GMT

This fixed the error.

Would you then do some sort of Proc Append loop to concatenate the tables?

Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists

Kurt_Bremser — Mon, 21 Aug 2017 13:58:21 GMT

@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;

Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists

acemanhattan — Mon, 21 Aug 2017 14:04:33 GMT

Not sure the appropriate way to mark as solution when the solution is spread across a few posts, but this worked great. Turned a 2 hour query into a 12 second query. Thanks.

Re: Calling 2 Macro Variables in Ordered Pairs from 2 Lists

Ron_MacroMaven — Mon, 21 Aug 2017 15:45:36 GMT

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