/************************************************************************************************ Module: dabt_create_files_for_rnt Function: This macro creates the required files for aggregation and filter purpose (label, if, keep, retain, reset files), for the given source table. Authors: BIS Team Date: 06 October 2008 SAS: 9.2 Called-by: dabt_src_tbl_ext macro Calls: 1. dabt_err_chk macro 2. dabt_param_update macro Datasets used: The dataset LIB_STG.KEEP_REQUIRED_COLUMNS is used to obtain the information of the dimensions of the variables that needs to be created. LIB_STG.LEVEL_DETAILS is used to obtain the event date information. LIB_STG.ABT_INFO is used to obtain the target period information. Logic: 1. All the dimensions to be considered for a given source table are picked up from LIB_STG.KEEP_REQUIRED_COLUMNS. For a given source table, all the dimensions like TRANSACTION_TYPE_CD, CHANNEL_CD and their respective name in LIB_STG.VARIABLE_LIST_RNT is picked up from the keep required columns table. This information is stored in the column KEEP_COLUMN_NAME. 2. The appropriate load date is obtained depending on the whether the macro is called for outcome or input, and whether the abt is event based or not. 3. The input var data is sorted by the time period, required dimensions and source table. 4. The LABEL file is created by taking all the variable names as given in DERIVED_VAR_NM of variable list table and label as given in DERIVED_VAR_LBL. This is stored at &bankfdn_file_path. The name of the file is F____LABEL.txt. 5. The KEEP file is created by taking all the variable names as given in DERIVED_VAR_NM of variable list table. This is stored at &bankfdn_file_path. The name of the file is F____KEEP.txt. Only these variables and the key variables will be kept in the final aggregated dataset. 6. The RETAIN file is created by taking all the variable names as given in DERIVED_VAR_NM of variable list table.The variable _D which contains the latest date field is also retained. This is stored at &bankfdn_file_path. The name of the file is F____RETAIN.txt. These variables are retained for each account (if account is the variable level, for each customer if customer is the variable level and so on). 7. The RESET file is created by taking all the variable names as given in DERIVED_VAR_NM of variable list table. The variable _D which contains the latest date field is also resetted. This is stored at &bankfdn_file_path. The name of the file is F____RESET.txt. These variables are resetted to null after each account calculation (if account is the variable level, for each customer if customer is the variable level and so on). 8. The IF file is created. This is stored at &bankfdn_file_path. The name of the file is F____IF.txt. The if statement is created for all the dimensions as obtained in step 1 and for the requried time period. 9. After each proc or data step the macro dabt_err_chk is called by passing SQL or DATA respectively, which checks for any error in the proc or data step and assigns the appropriate error code. Parameters: INPUT: 1. src : Variable source table, like SNP for account_snapshot, TRN for account_transaction_fact and so on. 2. trgt_tbl : Target table sk. 3. var_type : Type of variable, that is behavioural or semi behavioural. 4. in_var_ds : Two level input variable list dataset, like D__VAR_. This table is obtained by subsetting LIB_STG.VARIABLE_LIST_RNT for the given source. OUTPUT: Files (5) created by the macro (stored at &bankfdn_file_path). ************************************************************************************************/ %macro dabt_create_files_for_rnt(src=,trgt_tbl=,var_type=,in_var_ds=,subject_level_sk=) / store nosecure des='dabt_create_files_for_rnt'; &soc_internal. option nomprint nomlogic nosymbolgen; %let set_opt = ; /* To set the mlogic mprint option */ %if %sysfunc(strip("&soc_internal")) eq "" %then %do; %let set_opt = option nomprint nomlogic nosymbolgen;; %end; %let src = &src; %let trgt_tbl = &trgt_tbl; %let var_type = &var_type; %let in_var_ds = &in_var_ds; %let subject_level_sk = &subject_level_sk; /* Set the outcome flag depending on whether the macro is called for outcome var or input */ %if %kupcase("&var_type") eq "OUT_RNT" %then %do; %let out_flg = Y; %end; %else %do; %let out_flg = N; %end; %if %quote(&trgt_tbl) ne %then %do; /* Get the order by date column name */ %let rnt_usage_col_nm = ; %let rnt_usage_dt_sk = ; proc sql noprint; select order_by_date_column_nm, order_by_date_sk into :rnt_usage_col_nm, :rnt_usage_dt_sk from &in_var_ds.; quit; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); &set_opt; %let rnt_usage_col_nm = %kupcase(&rnt_usage_col_nm); %let rnt_usage_dt_sk = &rnt_usage_dt_sk; %let rnt_data_type_cd = ; /* Obtain the data type of order by date column */ proc sql noprint; select keep_column_data_type into :rnt_data_type_cd from &lib_stg..keep_required_columns where VAR_SOURCE_TABLE_SK = &src. and level_sk = &subject_level_sk. and kupcase(strip(column_info_type)) = "RNT_USG" and kupcase(outcome_flg) = "&out_flg" and kupcase(keep_column_name) = "&rnt_usage_col_nm"; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); quit; &set_opt; %let rnt_data_type_cd = %kupcase(&rnt_data_type_cd); %let event_dt_exst = ; /* Obtain the event date column name */ proc sql noprint; select event_date_nm into :event_dt_exst from &lib_stg..level_details where level_sk = &subject_level_sk.; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); quit; &set_opt; %let event_dt_exst = &event_dt_exst; %let src_time_freq = ; %let src_time_align = ; %let src_date_exp = ; %let src_dttm_exp = ; /* Obtain the source data grain for the given abt */ proc sql noprint; select source_data_time_grain_cd, source_data_time_align, source_data_time_int_date_exp, source_data_time_int_dttm_exp into :src_time_freq, :src_time_align, :src_date_exp, :src_dttm_exp from &lib_stg..abt_info; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); quit; &set_opt; %let src_time_freq = %kupcase(&src_time_freq); %let src_time_align = &src_time_align; %if %kupcase("&var_type") eq "OUT_RNT" %then %do; %if "&src_time_freq" ne "DAY" %then %do; %let adj_evt_date_col_nm = dabt_adj_event_out_date; %let adj_evt_dttm_col_nm = dabt_adj_event_out_dttm; %let event_bld_cap_date = dabt_adj_event_date; %let event_bld_cap_dttm = dabt_adj_event_dttm; %end; %else %do; %let adj_evt_date_col_nm = dabt_event_out_date; %let adj_evt_dttm_col_nm = dabt_event_out_dttm; %let event_bld_cap_date = dabt_event_date; %let event_bld_cap_dttm = dabt_event_dttm; %end; %end; %else %do; %if "&src_time_freq" ne "DAY" %then %do; %let adj_evt_date_col_nm = dabt_adj_event_date; %let adj_evt_dttm_col_nm = dabt_adj_event_dttm; %let event_bld_cap_date = ; %let event_bld_cap_dttm = ; %end; %else %do; %let adj_evt_date_col_nm = dabt_event_date; %let adj_evt_dttm_col_nm = dabt_event_dttm; %let event_bld_cap_date = ; %let event_bld_cap_dttm = ; %end; %end; %let src_time_exp = ; %let event_dt = ; /* Depending on the datatype of order by date set the event, outcome and build date */ %if "&rnt_data_type_cd" eq "DAT" %then %do; %let src_time_exp = &src_date_exp; %if &event_dt_exst ne %then %do; %let event_dt = &adj_evt_date_col_nm; %let event_bld_cap_dt = &event_bld_cap_date; %end; %let BUILD_DT = &DABT_LOAD_USER_INPUT_DATE; %if %kupcase("&var_type") eq "OUT_RNT" %then %do; %let BUILD_DT = &DABT_OUTCOME_END_DATE; %let BUILD_CAP_DT = &DABT_LOAD_USER_INPUT_DATE; %end; %end; %else %do; %let src_time_exp = &src_dttm_exp; %if &event_dt_exst ne %then %do; %let event_dt = &adj_evt_dttm_col_nm; %let event_bld_cap_dt = &event_bld_cap_dttm; %end; %let BUILD_DT = &DABT_LOAD_USER_INPUT_DTTM; %if %kupcase("&var_type") eq "OUT_RNT" %then %do; %let BUILD_DT = &DABT_OUTCOME_END_DTTM; %let BUILD_CAP_DT = &DABT_LOAD_USER_INPUT_DTTM; %end; %end; /* Obtain the count of dimensions that exists in the keep_required_columns for the given source table and level */ %let dim_cnt = 0; /* i18nOK:Begin */ proc sql noprint; select count(*) into :dim_cnt from &lib_stg..keep_required_columns where VAR_SOURCE_TABLE_SK = &src. and level_sk = &subject_level_sk. and kupcase(strip(column_info_type)) = "RNT_DIM" and kupcase(outcome_flg) = "&out_flg"; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); quit; &set_opt; /* i18nOK:End */ %let dim_cnt = &dim_cnt; %let var_dim = ; %let var_dim_com = ; /* If any dimension exists then the following is executed */ %if &dim_cnt gt 0 %then %do; /* All the dimensions to be considered for a given source table are picked up from LIB_STG.KEEP_REQUIRED_COLUMNS. For a given source table, all the dimensions like TRANSACTION_TYPE_CD, CHANNEL_CD and their respective name in LIB_STG.VARIABLE_LIST_RNT is picked up from the keep required columns table. This information is stored in the column KEEP_COLUMN_NAME. */ proc sql noprint; select distinct keep_column_name into :var_dim separated by ' ' from &lib_stg..keep_required_columns where VAR_SOURCE_TABLE_SK = &src. and level_sk = &subject_level_sk. and kupcase(strip(column_info_type)) = "RNT_DIM" and kupcase(outcome_flg) = "&out_flg"; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); quit; &set_opt; data _NULL_; call symput('var_dim_com',prxchange('s/\s+/,/', -1, "&var_dim"));/* i18nOK:Line */ run; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=DATA); &set_opt; %let var_tot = %eval(%sysfunc(countc(%quote(&var_dim_com),","))+1); %end; %let out_cap_flg = ; /* If the process is for outcome variable then the following will be executed */ %if %sysfunc(kupcase("&var_type")) eq "OUT_RNT" %then %do; /* Obtian the outcome cap flag */ proc sql noprint; select bld_dt_cap_flg into :out_cap_flg from &lib_stg..abt_info where target_period_cnt >= 0; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); quit; &set_opt; %let out_cap_flg = %kupcase(&out_cap_flg); %end; /*sinpep: Modified code to handle too long data set name. Macro variable used, so that the change has to be done only at one place. This macro variable is used in all places where the dataset name was present. */ /* Issue reported with track # 7611052013 dated 24-Jul-2013 */ /* SAS Problem Note 50630 through which this changed code/macro is delivered*/ %let out_srt_var_ds = &lib_scr_var..vd_&src._&subject_level_sk._&rnt_usage_dt_sk._&var_type._st; /*sinpep: End of Modified code for SAS Note 50630 issue */ /* The input data is sorted by the time period, required dimensions and source table */ proc sort data = &in_var_ds out=&out_srt_var_ds; /* by TIME_PERIOD_FROM TIME_PERIOD_TO TIME_FREQ_INT_DATE_EXP TIME_FREQ_INT_DTTM_EXP TIME_FREQUENCY_CD &var_dim. VAR_SOURCE_TABLE_SK;*/ by VARIABLE_GROUP_NO; run; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=DATA); &set_opt; /* The variable level keys are obtained for the given variable level. */ %let key = ; proc sql noprint; select level_key_column_nm into :key from &lib_stg..level_details where level_sk = &subject_level_sk.; quit; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); &set_opt; %let key = &key; %let key_cnt = %eval(%sysfunc(countc(%quote(&key),","))+1); /* The IF file is created by considering the time period and selection criteria. This is stored at &bankfdn_file_path. The name of the file is F_____QUERY.txt. */ data _null_; set &out_srt_var_ds end=last_rec; by VARIABLE_GROUP_NO; length derived_variable_condition SELECTION_CRITERIA $32767; length TIME_FROM TIME_TO $100; length LOAD_FREQ $1. TIME_FREQ_INT_EXP $10.; retain TIME_FREQ_INT_EXP LOAD_FREQ TIME_FROM TIME_TO SELECTION_CRITERIA; fname="tempfile"; var_group = put(VARIABLE_GROUP_NO,12.); file_nm = "&bankfdn_file_path./f_&src._&subject_level_sk._"||strip(var_group) || "_&var_type._query.sas"; file fname filevar=file_nm;/* i18nOK:Line */ /* i18nOK:Begin */ if first.VARIABLE_GROUP_NO then do; /*sinpep: Modified code to handle concatenation of selection criteria across variable. */ /* Issue reported with track # 7611052008 in Aug-2013 */ /* SAS Problem Note 50630 through which this changed code/macro is delivered*/ SELECTION_CRITERIA = ''; /*sinpep: End of Modified code for SAS Note 50630 issue */ put '/* The macro variable DABT_OUTPUT_TABLE_NM will get resolved to the appropriate output name during execution*/'; put '/* The macro variable DABT_INPUT_TABLE_NM will get resolved to the appropriate input name during execution, created in the previous level */'; /* Create proc sql */ put "proc sql noprint &sql_option.;"; put 'create table &DABT_OUTPUT_TABLE_NM as '; derived_variable_condition = 'select ' || /* Obtain the number of columns which are part of the key */ %do key_j = 1 %to &key_cnt; %let key_tkn = %scan(%quote(&key),&key_j,%str(,)); "t1.&key_tkn., " %end; ; put derived_variable_condition; put ' REFERENCE_DATE, REFERENCE_DTTM,'; %if "&rnt_data_type_cd" eq "DAT" %then %do; TIME_FREQ_INT_EXP = TIME_FREQ_INT_DATE_EXP; %end; %else %do; TIME_FREQ_INT_EXP = TIME_FREQ_INT_DTTM_EXP; %end; LOAD_FREQ = substr(TIME_FREQUENCY_CD,1,1); /* Get the time from and time to dates to put the filter on */ %if &event_dt_exst ne %then %do; TIME_FROM = "&event_dt._" || strip(TIME_PERIOD_FROM-1) || strip(LOAD_FREQ);/* i18nOK:Line */ TIME_TO = "&event_dt._" || strip(TIME_PERIOD_TO) || strip(LOAD_FREQ);/* i18nOK:Line */ %end; %else %do; TIME_FROM = intnx(strip(TIME_FREQ_INT_EXP),&BUILD_DT,-strip(TIME_PERIOD_FROM-1),"&src_time_align");/* i18nOK:Line */ TIME_TO = intnx(strip(TIME_FREQ_INT_EXP),&BUILD_DT,-strip(TIME_PERIOD_TO),"&src_time_align");/* i18nOK:Line */ %end; /* if build date cap is to be applied for outcome variable then, adjust the time to accordingly */ %if &out_cap_flg eq Y %then %do; %if &event_dt_exst ne %then %do; TIME_TO = " &event_bld_cap_dt AND &rnt_usage_col_nm > " || strip(TIME_TO); %end; %else %do; TIME_TO = MAX(&BUILD_CAP_DT,TIME_TO);/* i18nOK:Line */ %end; %end; /* Get the condition for selection criteria */ %if &var_dim. ne %then %do; %do var_cnt = 1 %to &var_tot; %let var_dim_tkn = %scan(%quote(&var_dim_com),&var_cnt,%str(,));/* i18nOK:Line */ if strip(&var_dim_tkn.) ne "" then do; SELECTION_CRITERIA = cat(ktrim(kleft(SELECTION_CRITERIA)), " AND &var_dim_tkn. IN ( ", ktrim(kleft(&var_dim_tkn.)),' )'); end; %end; %end; end; if last.VARIABLE_GROUP_NO then do; derived_variable_condition = ' ' || strip(SELECT_COLUMN_NM) || ' AS ' || strip(DERIVED_VAR_NM); put derived_variable_condition; put 'from &DABT_INPUT_TABLE_NM. t1,'; put " (select &key, MAX(&rnt_usage_col_nm) as DABT_MAX_RNT_TR_DT "; put ' from &DABT_INPUT_TABLE_NM.'; derived_variable_condition = " where &rnt_usage_col_nm <= " || strip(TIME_FROM) || " AND &rnt_usage_col_nm > " || strip(TIME_TO); put derived_variable_condition; derived_variable_condition = " " || ktrim(kleft(SELECTION_CRITERIA)); put derived_variable_condition; put " GROUP BY &key.) t2"; derived_variable_condition = "where t1.&rnt_usage_col_nm. = t2.DABT_MAX_RNT_TR_DT " || ktrim(kleft(SELECTION_CRITERIA)); put derived_variable_condition; %do key_j = 1 %to &key_cnt; %let key_tkn = %scan(%quote(&key),&key_j,%str(,)); put " AND t1.&key_tkn. = t2.&key_tkn. "; %end; put ';'; put 'quit;'; end; else do; derived_variable_condition = ' ' || strip(SELECT_COLUMN_NM) || ' AS ' || strip(DERIVED_VAR_NM) || ', '; put derived_variable_condition; end; /* i18nOK:End */ run; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=DATA); &set_opt; /* If the debug flag is set to N, then the temporary datasets created above are deleted */ %if %sysfunc(kupcase("&DABT_DEBUG_FLG")) eq "N" %then %do; proc sql noprint; drop table &out_srt_var_ds; quit; /* Obtain the error code for the previous sql executed, and retain the maximum error code */ %dabt_err_chk(type=SQL); &set_opt; %end; %end; %mend dabt_create_files_for_rnt;