Hi guys,
I am using the code below and I am stuck with the how-to extract data that are related to the age band of my data set (e.g. how many genuine and how many fraudulent transactions are populated in specific age bands from a particular merchant ID).
Can someone please assist me?
Your prompt reply will be highly appreciated!
Kind regards,
Chris
%let username = xxx;
%let password = xxx;
options symbolgen mlogic mprint;
%let db2connect = connect to db2 (database=FMRL01 user="&username" password="&password" read_isolation_level=ur);
libname RH db2 database=FMRL01 schema=RPTCORE user="&username" password="&password" dbindex=yes read_isolation_level=ur;
libname XXX '/anenv/prd/crd/crd2/XXX';
proc sql;
&db2connect;
create table XXX.ex_XXX as
select
cmx_tran_id
,rhx_date_key
,rhx_time_key
,rqo_tran_date_alt
,rqo_tran_time_alt
,smh_multi_org_node_key
,smh_multi_org_name
,rrr_score_1
,rrr_score_4
,rrr_action_code
,hqo_card_num
,tca_mod_amt
,tca_merch_curr_code
,ucm_pos
,tca_auth_sys_dec
,tca_auth_sys_info
,ucm_ch_evrfy
,hct_term_owner_name
,hct_term_owner_id
,hct_term_city
,hct_term_state
,hct_term_post_code
,hct_term_cntry_code
,hct_mer_mcc
,rob_action_code
,rob_action_until_date
,rob_cust_ind
,rua_ind_027
,rrf_rule_id
,cqf_rule_c_id
,cmx_user_fraud_c_ind
,rhx_score_1_sas_match_ind
,rur_8byte_string_001
,rur_4byte_string_001
,ucm_avs_resp
,ucm_cust_present
,ucm_msg_exp_date
,xqo_cust_post_code
,rua_numeric_042
,rua_numeric_002 /* issue date */
,rua_numeric_003
,rua_numeric_006
,rua_numeric_007
,rua_numeric_039 /* account open date */
,rur_numeric_001
,rur_numeric_002
,rur_numeric_003
,rur_numeric_007
,rua_numeric_010
,rua_20byte_string_005
,rua_2byte_string_001
,rua_8byte_string_002
,rua_30byte_string_001
,rur_ind_001
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,721 ,8 ), ieee8.0 ) else . end as _c_prev_mer_amt_1 format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,729 ,8 ), ieee8.0 ) else . end as _c_prev_mer_amt_2 format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,737 ,8 ), ieee8.0 ) else . end as _c_prev_mer_amt_3 format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,745 ,8 ), ieee8.0 ) else . end as _c_prev_mer_amt_4 format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,689 ,8 ), ieee8.0 ) else . end as _c_prev_mer_dt_1 format=datetime21.2
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,697 ,8 ), ieee8.0 ) else . end as _c_prev_mer_dt_2 format=datetime21.2
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,705 ,8 ), ieee8.0 ) else . end as _c_prev_mer_dt_3 format=datetime21.2
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,713 ,8 ), ieee8.0 ) else . end as _c_prev_mer_dt_4 format=datetime21.2
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,753 ,24 ) else '' end as _c_prev_mer_ext_1 format=$24. /* prev merch mcc */
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,777 ,24 ) else '' end as _c_prev_mer_ext_2 format=$24.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,801 ,24 ) else '' end as _c_prev_mer_ext_3 format=$24.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,825 ,24 ) else '' end as _c_prev_mer_ext_4 format=$24.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,881 ,16 ) else '' end as _c_prev_mer_id_1 format=$16.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,897 ,16 ) else '' end as _c_prev_mer_id_2 format=$16.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,913 ,16 ) else '' end as _c_prev_mer_id_3 format=$16.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,929 ,16 ) else '' end as _c_prev_mer_id_4 format=$16.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,593 ,24 ) else '' end as _c_prev_mer_nm_1 format=$24.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,617 ,24 ) else '' end as _c_prev_mer_nm_2 format=$24.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,641 ,24 ) else '' end as _c_prev_mer_nm_3 format=$24.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,665 ,24 ) else '' end as _c_prev_mer_nm_4 format=$24.
,case when (50297 <= v00_build_num) then input(substr(v00_variable_data ,977 ,8 ), ieee8.0 ) else . end as _c_atm_history format=best.
,case when (50350 <= v00_build_num) then input(substr(v00_variable_data ,1065 ,8 ), ieee8.0 ) else . end as _c_arcot_dt format=best.
,case when (50882 <= v00_build_num) then input(substr(v00_variable_data ,1393 ,8 ), ieee8.0 ) else . end as _c_jld_array_1 format=best.
,case when (50882 <= v00_build_num) then input(substr(v00_variable_data ,1401 ,8 ), ieee8.0 ) else . end as _c_jld_array_2 format=best.
,case when (50882 <= v00_build_num) then input(substr(v00_variable_data ,1409 ,8 ), ieee8.0 ) else . end as _c_jld_array_3 format=best.
,case when (50882 <= v00_build_num) then input(substr(v00_variable_data ,1417 ,8 ), ieee8.0 ) else . end as _c_jld_array_4 format=best.
,case when (50882 <= v00_build_num) then input(substr(v00_variable_data ,1425 ,8 ), ieee8.0 ) else . end as _c_jld_array_5 format=best.
,case when (50882 <= v00_build_num) then input(substr(v00_variable_data ,1385 ,8 ), ieee8.0 ) else . end as _c_jld_array_dt format=datetime21.2
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1705 ,8 ), ieee8.0 ) else . end as _c_token_fst_1 format=best.
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1713 ,8 ), ieee8.0 ) else . end as _c_token_fst_2 format=best.
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1721 ,8 ), ieee8.0 ) else . end as _c_token_fst_3 format=best.
,case when (51138 <= v00_build_num) then substr(v00_variable_data ,1609 ,32 ) else '' end as _c_token_id_1 format=$32.
,case when (51138 <= v00_build_num) then substr(v00_variable_data ,1641 ,32 ) else '' end as _c_token_id_2 format=$32.
,case when (51138 <= v00_build_num) then substr(v00_variable_data ,1673 ,32 ) else '' end as _c_token_id_3 format=$32.
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1729 ,8 ), ieee8.0 ) else . end as _c_token_lst_1 format=best.
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1737 ,8 ), ieee8.0 ) else . end as _c_token_lst_2 format=best.
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1745 ,8 ), ieee8.0 ) else . end as _c_token_lst_3 format=best.
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1753 ,8 ), ieee8.0 ) else . end as _c_token_val_1 format=best.
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1761 ,8 ), ieee8.0 ) else . end as _c_token_val_2 format=best.
,case when (51138 <= v00_build_num) then input(substr(v00_variable_data ,1769 ,8 ), ieee8.0 ) else . end as _c_token_val_3 format=best.
,case when (50282 <= v00_build_num) and (v00_build_num <= 50628) then input(substr(v00_variable_data ,97 ,8 ), ieee8.0 ) else . end as _c_default_blk_atm format=best.
,case when (50282 <= v00_build_num) and (v00_build_num <= 50628) then input(substr(v00_variable_data ,105 ,8 ), ieee8.0 ) else . end as _c_default_blk_cnp format=best.
,case when (50282 <= v00_build_num) and (v00_build_num <= 50628) then input(substr(v00_variable_data ,81 ,8 ), ieee8.0 ) else . end as _c_default_blk_days format=best.
,case when (50282 <= v00_build_num) and (v00_build_num <= 50628) then input(substr(v00_variable_data ,89 ,8 ), ieee8.0 ) else . end as _c_default_blk_pos format=best.
,case when (50283 <= v00_build_num) then input(substr(v00_variable_data ,153 ,8 ), ieee8.0 ) else . end as _c_flexi_blk_atm format=best.
,case when (50283 <= v00_build_num) then input(substr(v00_variable_data ,169 ,8 ), ieee8.0 ) else . end as _c_flexi_blk_cnp format=best.
,case when (50283 <= v00_build_num) then input(substr(v00_variable_data ,145 ,8 ), ieee8.0 ) else . end as _c_flexi_blk_date format=best.
,case when (50283 <= v00_build_num) then input(substr(v00_variable_data ,161 ,8 ), ieee8.0 ) else . end as _c_flexi_blk_pos format=best.
,case when (51259 <= v02_build_num) then substr(v02_variable_data ,1 ,8 ) else '' end as _m_merchant_post_code format=$8.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,849 ,8 ), ieee8.0 ) else . end as _c_prev_mer_vel_cnt format=best.
,case when (50297 <= v00_build_num) then input(substr(v00_variable_data ,977 ,8 ), ieee8.0 ) else . end as _c_atm_history format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,377 ,8 ), ieee8.0 ) else . end as _c_betting_dt_1 format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,385 ,8 ), ieee8.0 ) else . end as _c_betting_dt_2 format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,393 ,8 ), ieee8.0 ) else . end as _c_betting_val_1 format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,401 ,8 ), ieee8.0 ) else . end as _c_betting_val_2 format=best.
,case when (50639 <= v00_build_num) then input(substr(v00_variable_data ,81 ,8 ), ieee8.0 ) else . end as _c_exclude_count format=best.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,65 ,8 ) else '' end as _c_region format=$8.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,353 ,8 ) else '' end as _c_infer_country format=$8.
,case when (51967 <= v00_build_num) then input(substr(v00_variable_data ,1145 ,8 ), ieee8.0 ) else . end as _c_token_dt format=best.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,857 ,8 ), ieee8.0 ) else . end as _c_prev_mer_vel_fdt format=best.
,case when (53129 <= v00_build_num) then input(substr(v00_variable_data ,2457 ,8 ), ieee8.0 ) else . end as _c_token_risk format=best.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,25 ,8 ) else '' end as _c_ms_marker_cpp_current format=$8.
,case when (50282 <= v00_build_num) then input(substr(v00_variable_data ,41 ,8 ), ieee8.0 ) else . end as _c_ms_marker_cpp_date format=best.
,case when (50282 <= v00_build_num) then substr(v00_variable_data ,33 ,8 ) else '' end as _c_ms_marker_cpp_previous format=$8.
,case when (50322 <= v01_build_num) then input(substr(v01_variable_data ,41 ,8 ), ieee8.0 ) else . end as _a_address_date_distance format=best.
,case when (50322 <= v01_build_num) then substr(v01_variable_data ,17 ,8 ) else '' end as _a_address_postareas format=$8.
,case when (54002 <= v02_build_num) then input(substr(v02_variable_data ,137 ,8 ), ieee8.0 ) else . end as _m_first_seen format=datetime21.2
,case when (53012 <= v00_build_num) then input(substr(v00_variable_data ,2441 ,8 ), ieee8.0 ) else . end as _c_atc_curr_ol format=best.
,case when (53012 <= v00_build_num) then input(substr(v00_variable_data ,2449 ,8 ), ieee8.0 ) else . end as _c_atc_offline_auth_today format=best.
,case when (53012 <= v00_build_num) then input(substr(v00_variable_data ,2433 ,8 ), ieee8.0 ) else . end as _c_atc_prev_ol format=best.
from connection to db2
(
select
a.cmx_tran_id
,a.rhx_date_key
,a.rhx_time_key
,a.rqo_tran_date_alt
,a.rqo_tran_time_alt
,a.smh_multi_org_node_key
,a.smh_multi_org_name
,a.rrr_score_1
,a.rrr_score_4
,a.rrr_action_code
,a.hqo_card_num
,a.tca_mod_amt
,a.tca_merch_curr_code
,a.ucm_pos
,a.tca_auth_sys_dec
,a.tca_auth_sys_info
,a.ucm_ch_evrfy
,a.hct_term_owner_name
,a.hct_term_owner_id
,a.hct_term_city
,a.hct_term_state
,a.hct_term_post_code
,a.hct_term_cntry_code
,a.hct_mer_mcc
,a.rob_action_code
,a.rob_action_until_date
,a.rob_cust_ind
,a.rua_ind_027
,a.rrf_rule_id
,a.cqf_rule_c_id
,a.cmx_user_fraud_c_ind
,a.rhx_score_1_sas_match_ind
,a.rur_8byte_string_001
,a.rur_4byte_string_001
,a.ucm_avs_resp
,a.ucm_cust_present
,a.ucm_msg_exp_date
,a.xqo_cust_post_code
,a.rua_numeric_042
,a.rua_numeric_002 /* issue date */
,a.rua_numeric_003
,a.rua_numeric_006
,a.rua_numeric_007
,a.rua_numeric_039 /* account open date */
,a.rur_numeric_001
,a.rur_numeric_002
,a.rur_numeric_003
,a.rur_numeric_007
,a.rua_numeric_010
,a.rua_20byte_string_005
,a.rua_2byte_string_001
,a.rua_8byte_string_002
,a.rua_30byte_string_001
,a.rur_ind_001
,b.v00_build_num
,b.v01_build_num
,b.v02_build_num
,b.v00_variable_data
,b.v01_variable_data
,b.v02_variable_data
from
rptcore.frh_ccca a
inner join
rptcore.frh_ccca_vxx b
on a.cmx_tran_id = b.cmx_tran_id
where
a.rhx_date_key >= '2019-09-01'
and
(
a.hct_term_owner_name like '%XXX%'
)
);
disconnect from db2;
quit;
data extract_vars;
set christos.ex_XXX;
format _c_ms_marker_cpp_date date9.;
length merch_country curr_name curr_code $ 100 Name $28 US_State $2 Anti $1;
/* Merchant country */
if hct_term_cntry_code = '004' then merch_country="Afghanistan";
else if hct_term_cntry_code = '008' then merch_country="Albania";
else if hct_term_cntry_code = '010' then merch_country="Antarctica";
else if hct_term_cntry_code = '012' then merch_country="Algeria";
else if hct_term_cntry_code = '016' then merch_country="American Samoa";
else if hct_term_cntry_code = '020' then merch_country="Andorra";
else if hct_term_cntry_code = '024' then merch_country="Angola";
else if hct_term_cntry_code = '028' then merch_country="Antigua and Barbuda";
else if hct_term_cntry_code = '031' then merch_country="Azerbaijan";
else if hct_term_cntry_code = '032' then merch_country="Argentina";
else if hct_term_cntry_code = '036' then merch_country="Australia";
else if hct_term_cntry_code = '040' then merch_country="Austria";
else if hct_term_cntry_code = '044' then merch_country="Bahamas";
else if hct_term_cntry_code = '048' then merch_country="Bahrain";
else if hct_term_cntry_code = '050' then merch_country="Bangladesh";
else if hct_term_cntry_code = '051' then merch_country="Armenia";
else if hct_term_cntry_code = '052' then merch_country="Barbados";
else if hct_term_cntry_code = '056' then merch_country="Belgium";
else if hct_term_cntry_code = '060' then merch_country="Bermuda";
else if hct_term_cntry_code = '064' then merch_country="Bhutan";
else if hct_term_cntry_code = '068' then merch_country="Bolivia, Plurinational State of";
else if hct_term_cntry_code = '070' then merch_country="Bosnia and Herzegovina";
else if hct_term_cntry_code = '072' then merch_country="Botswana";
else if hct_term_cntry_code = '074' then merch_country="Bouvet Island";
else if hct_term_cntry_code = '076' then merch_country="Brazil";
else if hct_term_cntry_code = '084' then merch_country="Belize";
else if hct_term_cntry_code = '086' then merch_country="British Indian Ocean Territory";
else if hct_term_cntry_code = '090' then merch_country="Solomon Islands";
else if hct_term_cntry_code = '092' then merch_country="Virgin Islands, British";
else if hct_term_cntry_code = '096' then merch_country="Brunei Darussalam";
else if hct_term_cntry_code = '100' then merch_country="Bulgaria";
else if hct_term_cntry_code = '104' then merch_country="Myanmar";
else if hct_term_cntry_code = '108' then merch_country="Burundi";
else if hct_term_cntry_code = '112' then merch_country="Belarus";
else if hct_term_cntry_code = '116' then merch_country="Cambodia";
else if hct_term_cntry_code = '120' then merch_country="Cameroon";
else if hct_term_cntry_code = '124' then merch_country="Canada";
else if hct_term_cntry_code = '132' then merch_country="Cape Verde";
else if hct_term_cntry_code = '136' then merch_country="Cayman Islands";
else if hct_term_cntry_code = '140' then merch_country="Central African Republic";
else if hct_term_cntry_code = '144' then merch_country="Sri Lanka";
else if hct_term_cntry_code = '148' then merch_country="Chad";
else if hct_term_cntry_code = '152' then merch_country="Chile";
else if hct_term_cntry_code = '156' then merch_country="China";
else if hct_term_cntry_code = '158' then merch_country="Taiwan, Province of China";
else if hct_term_cntry_code = '162' then merch_country="Christmas Island";
else if hct_term_cntry_code = '166' then merch_country="Cocos (Keeling) Islands";
else if hct_term_cntry_code = '170' then merch_country="Colombia";
else if hct_term_cntry_code = '174' then merch_country="Comoros";
else if hct_term_cntry_code = '175' then merch_country="Mayotte";
else if hct_term_cntry_code = '178' then merch_country="Congo";
else if hct_term_cntry_code = '180' then merch_country="Congo, The Democratic Republic of";
else if hct_term_cntry_code = '184' then merch_country="Cook Islands";
else if hct_term_cntry_code = '188' then merch_country="Costa Rica";
else if hct_term_cntry_code = '191' then merch_country="Croatia";
else if hct_term_cntry_code = '192' then merch_country="Cuba";
else if hct_term_cntry_code = '196' then merch_country="Cyprus";
else if hct_term_cntry_code = '203' then merch_country="Czechia";
else if hct_term_cntry_code = '204' then merch_country="Benin";
else if hct_term_cntry_code = '208' then merch_country="Denmark";
else if hct_term_cntry_code = '212' then merch_country="Dominica";
else if hct_term_cntry_code = '214' then merch_country="Dominican Republic";
else if hct_term_cntry_code = '218' then merch_country="Ecuador";
else if hct_term_cntry_code = '222' then merch_country="El Salvador";
else if hct_term_cntry_code = '226' then merch_country="Equatorial Guinea";
else if hct_term_cntry_code = '231' then merch_country="Ethiopia";
else if hct_term_cntry_code = '232' then merch_country="Eritrea";
else if hct_term_cntry_code = '233' then merch_country="Estonia";
else if hct_term_cntry_code = '234' then merch_country="Faroe Islands";
else if hct_term_cntry_code = '238' then merch_country="Falkland Islands (Malvinas)";
else if hct_term_cntry_code = '239' then merch_country="South Georgia & The South Sandwich Islands";
else if hct_term_cntry_code = '242' then merch_country="Fiji";
else if hct_term_cntry_code = '246' then merch_country="Finland";
else if hct_term_cntry_code = '248' then merch_country="Aland Islands";
else if hct_term_cntry_code = '250' then merch_country="France";
else if hct_term_cntry_code = '254' then merch_country="French Guiana";
else if hct_term_cntry_code = '258' then merch_country="French Polynesia";
else if hct_term_cntry_code = '260' then merch_country="French Southern Territories";
else if hct_term_cntry_code = '262' then merch_country="Djibouti";
else if hct_term_cntry_code = '266' then merch_country="Gabon";
else if hct_term_cntry_code = '268' then merch_country="Georgia";
else if hct_term_cntry_code = '270' then merch_country="Gambia";
else if hct_term_cntry_code = '275' then merch_country="Palestinian Territory, Occupied";
else if hct_term_cntry_code = '276' then merch_country="Germany";
else if hct_term_cntry_code = '288' then merch_country="Ghana";
else if hct_term_cntry_code = '292' then merch_country="Gibraltar";
else if hct_term_cntry_code = '296' then merch_country="Kiribati";
else if hct_term_cntry_code = '300' then merch_country="Greece";
else if hct_term_cntry_code = '304' then merch_country="Greenland";
else if hct_term_cntry_code = '308' then merch_country="Grenada";
else if hct_term_cntry_code = '312' then merch_country="Guadeloupe";
else if hct_term_cntry_code = '316' then merch_country="Guam";
else if hct_term_cntry_code = '320' then merch_country="Guatemala";
else if hct_term_cntry_code = '324' then merch_country="Guinea";
else if hct_term_cntry_code = '328' then merch_country="Guyana";
else if hct_term_cntry_code = '332' then merch_country="Haiti";
else if hct_term_cntry_code = '334' then merch_country="Heard and Mc Donald Islands";
else if hct_term_cntry_code = '336' then merch_country="Holy See (Vatican City State)";
else if hct_term_cntry_code = '340' then merch_country="Honduras";
else if hct_term_cntry_code = '344' then merch_country="Hong Kong";
else if hct_term_cntry_code = '348' then merch_country="Hungary";
else if hct_term_cntry_code = '352' then merch_country="Iceland";
else if hct_term_cntry_code = '356' then merch_country="India";
else if hct_term_cntry_code = '360' then merch_country="Indonesia";
else if hct_term_cntry_code = '364' then merch_country="Iran, Islamic Republic of";
else if hct_term_cntry_code = '368' then merch_country="Iraq";
else if hct_term_cntry_code = '372' then merch_country="Ireland";
else if hct_term_cntry_code = '376' then merch_country="Israel";
else if hct_term_cntry_code = '380' then merch_country="Italy";
else if hct_term_cntry_code = '384' then merch_country="Cote d'Ivoire";
else if hct_term_cntry_code = '388' then merch_country="Jamaica";
else if hct_term_cntry_code = '392' then merch_country="Japan";
else if hct_term_cntry_code = '398' then merch_country="Kazakstan";
else if hct_term_cntry_code = '400' then merch_country="Jordan";
else if hct_term_cntry_code = '404' then merch_country="Kenya";
else if hct_term_cntry_code = '408' then merch_country="Korea, Democratic People's Republic of";
else if hct_term_cntry_code = '410' then merch_country="Korea, Republic of";
else if hct_term_cntry_code = '414' then merch_country="Kuwait";
else if hct_term_cntry_code = '417' then merch_country="Kyrgyzstan";
else if hct_term_cntry_code = '418' then merch_country="Lao, People's Democratic Republic";
else if hct_term_cntry_code = '422' then merch_country="Lebanon";
else if hct_term_cntry_code = '426' then merch_country="Lesotho";
else if hct_term_cntry_code = '428' then merch_country="Latvia";
else if hct_term_cntry_code = '430' then merch_country="Liberia";
else if hct_term_cntry_code = '434' then merch_country="Libyan Arab Jamahiriya";
else if hct_term_cntry_code = '438' then merch_country="Liechtenstein";
else if hct_term_cntry_code = '440' then merch_country="Lithuania";
else if hct_term_cntry_code = '442' then merch_country="Luxembourg";
else if hct_term_cntry_code = '446' then merch_country="Macao";
else if hct_term_cntry_code = '450' then merch_country="Madagascar";
else if hct_term_cntry_code = '454' then merch_country="Malawi";
else if hct_term_cntry_code = '458' then merch_country="Malaysia";
else if hct_term_cntry_code = '462' then merch_country="Maldives";
else if hct_term_cntry_code = '466' then merch_country="Mali";
else if hct_term_cntry_code = '470' then merch_country="Malta";
else if hct_term_cntry_code = '474' then merch_country="Martinique";
else if hct_term_cntry_code = '478' then merch_country="Mauritania";
else if hct_term_cntry_code = '480' then merch_country="Mauritius";
else if hct_term_cntry_code = '484' then merch_country="Mexico";
else if hct_term_cntry_code = '492' then merch_country="Monaco";
else if hct_term_cntry_code = '496' then merch_country="Mongolia";
else if hct_term_cntry_code = '498' then merch_country="Moldova, Republic of";
else if hct_term_cntry_code = '499' then merch_country="Montenegro";
else if hct_term_cntry_code = '500' then merch_country="Montserrat";
else if hct_term_cntry_code = '504' then merch_country="Morocco";
else if hct_term_cntry_code = '508' then merch_country="Mozambique";
else if hct_term_cntry_code = '512' then merch_country="Oman";
else if hct_term_cntry_code = '516' then merch_country="Namibia";
else if hct_term_cntry_code = '520' then merch_country="Nauru";
else if hct_term_cntry_code = '524' then merch_country="Nepal";
else if hct_term_cntry_code = '528' then merch_country="Netherlands";
else if hct_term_cntry_code = '531' then merch_country="Curaçao";
else if hct_term_cntry_code = '533' then merch_country="Aruba";
else if hct_term_cntry_code = '534' then merch_country="Sint Maarten";
else if hct_term_cntry_code = '535' then merch_country="Bonaire, Sint Eustatius and Saba";
else if hct_term_cntry_code = '540' then merch_country="New Caledonia";
else if hct_term_cntry_code = '548' then merch_country="Vanuatu";
else if hct_term_cntry_code = '554' then merch_country="New Zealand";
else if hct_term_cntry_code = '558' then merch_country="Nicaragua";
else if hct_term_cntry_code = '562' then merch_country="Niger";
else if hct_term_cntry_code = '566' then merch_country="Nigeria";
else if hct_term_cntry_code = '570' then merch_country="Niue";
else if hct_term_cntry_code = '574' then merch_country="Norfolk Island";
else if hct_term_cntry_code = '578' then merch_country="Norway";
else if hct_term_cntry_code = '580' then merch_country="Northern Mariana Islands";
else if hct_term_cntry_code = '581' then merch_country="United States Minor Outlying Islands";
else if hct_term_cntry_code = '583' then merch_country="Micronesia, Federated States of";
else if hct_term_cntry_code = '584' then merch_country="Marshall Islands";
else if hct_term_cntry_code = '585' then merch_country="Palau";
else if hct_term_cntry_code = '586' then merch_country="Pakistan";
else if hct_term_cntry_code = '591' then merch_country="Panama";
else if hct_term_cntry_code = '598' then merch_country="Papua New Guinea";
else if hct_term_cntry_code = '600' then merch_country="Paraguay";
else if hct_term_cntry_code = '604' then merch_country="Peru";
else if hct_term_cntry_code = '608' then merch_country="Philippines";
else if hct_term_cntry_code = '612' then merch_country="Pitcairn";
else if hct_term_cntry_code = '616' then merch_country="Poland";
else if hct_term_cntry_code = '620' then merch_country="Portugal";
else if hct_term_cntry_code = '624' then merch_country="Guinea-Bissau";
else if hct_term_cntry_code = '626' then merch_country="Timor-Leste";
else if hct_term_cntry_code = '630' then merch_country="Puerto Rico";
else if hct_term_cntry_code = '634' then merch_country="Qatar";
else if hct_term_cntry_code = '638' then merch_country="Reunion";
else if hct_term_cntry_code = '642' then merch_country="Romania";
else if hct_term_cntry_code = '643' then merch_country="Russia Federation";
else if hct_term_cntry_code = '646' then merch_country="Rwanda";
else if hct_term_cntry_code = '652' then merch_country="Saint Barthélemy";
else if hct_term_cntry_code = '654' then merch_country="Saint Helena";
else if hct_term_cntry_code = '659' then merch_country="Saint Kitts & Nevis";
else if hct_term_cntry_code = '660' then merch_country="Anguilla";
else if hct_term_cntry_code = '662' then merch_country="Saint Lucia";
else if hct_term_cntry_code = '663' then merch_country="Saint Martin";
else if hct_term_cntry_code = '666' then merch_country="Saint Pierre and Miquelon";
else if hct_term_cntry_code = '670' then merch_country="Saint Vincent and the Grenadines";
else if hct_term_cntry_code = '674' then merch_country="San Marino";
else if hct_term_cntry_code = '678' then merch_country="Sao Tome and Principe";
else if hct_term_cntry_code = '682' then merch_country="Saudi Arabia";
else if hct_term_cntry_code = '686' then merch_country="Senegal";
else if hct_term_cntry_code = '688' then merch_country="Republic of Serbia";
else if hct_term_cntry_code = '690' then merch_country="Seychelles";
else if hct_term_cntry_code = '694' then merch_country="Sierra Leone";
else if hct_term_cntry_code = '702' then merch_country="Singapore";
else if hct_term_cntry_code = '703' then merch_country="Slovakia";
else if hct_term_cntry_code = '704' then merch_country="Vietnam";
else if hct_term_cntry_code = '705' then merch_country="Slovenia";
else if hct_term_cntry_code = '706' then merch_country="Somalia";
else if hct_term_cntry_code = '710' then merch_country="South Africa";
else if hct_term_cntry_code = '716' then merch_country="Zimbabwe";
else if hct_term_cntry_code = '724' then merch_country="Spain";
else if hct_term_cntry_code = '728' then merch_country="South Sudan";
else if hct_term_cntry_code = '732' then merch_country="Western Sahara";
else if hct_term_cntry_code = '736' then merch_country="Sudan";
else if hct_term_cntry_code = '740' then merch_country="Suriname";
else if hct_term_cntry_code = '744' then merch_country="Svalbard and Jan Mayen";
else if hct_term_cntry_code = '748' then merch_country="Swaziland";
else if hct_term_cntry_code = '752' then merch_country="Sweden";
else if hct_term_cntry_code = '756' then merch_country="Switzerland";
else if hct_term_cntry_code = '760' then merch_country="Syrian Arab Republic";
else if hct_term_cntry_code = '762' then merch_country="Tajikistan";
else if hct_term_cntry_code = '764' then merch_country="Thailand";
else if hct_term_cntry_code = '768' then merch_country="Togo";
else if hct_term_cntry_code = '772' then merch_country="Tokelau";
else if hct_term_cntry_code = '776' then merch_country="Tonga";
else if hct_term_cntry_code = '780' then merch_country="Trinidad and Tobago";
else if hct_term_cntry_code = '784' then merch_country="United Arab Emirates";
else if hct_term_cntry_code = '788' then merch_country="Tunisia";
else if hct_term_cntry_code = '792' then merch_country="Turkey";
else if hct_term_cntry_code = '795' then merch_country="Turkmenistan";
else if hct_term_cntry_code = '796' then merch_country="Turks and Caicos Islands";
else if hct_term_cntry_code = '798' then merch_country="Tuvalu";
else if hct_term_cntry_code = '800' then merch_country="Uganda";
else if hct_term_cntry_code = '804' then merch_country="Ukraine";
else if hct_term_cntry_code = '807' then merch_country="Macedonia, The Former Yugoslav Republic Of";
else if hct_term_cntry_code = '818' then merch_country="Egypt";
else if hct_term_cntry_code = '826' then merch_country="United Kingdom";
else if hct_term_cntry_code = '831' then merch_country="Guernsey";
else if hct_term_cntry_code = '832' then merch_country="Jersey";
else if hct_term_cntry_code = '833' then merch_country="Isle of Man";
else if hct_term_cntry_code = '834' then merch_country="Tanzania, United Republic of";
else if hct_term_cntry_code = '840' then merch_country="United States";
else if hct_term_cntry_code = '850' then merch_country="Virgin Islands, U.S.";
else if hct_term_cntry_code = '854' then merch_country="Burkina Faso";
else if hct_term_cntry_code = '858' then merch_country="Uruguay";
else if hct_term_cntry_code = '860' then merch_country="Uzbekistan";
else if hct_term_cntry_code = '862' then merch_country="Venezuela";
else if hct_term_cntry_code = '876' then merch_country="Wallis and Futuna";
else if hct_term_cntry_code = '882' then merch_country="Samoa";
else if hct_term_cntry_code = '887' then merch_country="Yemen";
else if hct_term_cntry_code = '894' then merch_country="Zambia";
else if hct_term_cntry_code = '926' then merch_country="Kosovo (temporary code)";
else if hct_term_cntry_code = '977' then merch_country="Turkish Rep N Cyprus (temporary code)";
/* Payment currency */
if tca_merch_curr_code = "008" then do; curr_name = "Lek"; curr_code = "ALL"; end;
else if tca_merch_curr_code = "012" then do; curr_name = "Algerian Dinar"; curr_code = "DZD"; end;
else if tca_merch_curr_code = "032" then do; curr_name = "Argentine Peso"; curr_code = "ARS"; end;
else if tca_merch_curr_code = "036" then do; curr_name = "Australian Dollar"; curr_code = "AUD"; end;
else if tca_merch_curr_code = "044" then do; curr_name = "Bahamian Dollar"; curr_code = "BSD"; end;
else if tca_merch_curr_code = "048" then do; curr_name = "Bahraini Dinar"; curr_code = "BHD"; end;
else if tca_merch_curr_code = "050" then do; curr_name = "Taka"; curr_code = "BDT"; end;
else if tca_merch_curr_code = "051" then do; curr_name = "Armenian Dram"; curr_code = "AMD"; end;
else if tca_merch_curr_code = "052" then do; curr_name = "Barbados Dollar"; curr_code = "BBD"; end;
else if tca_merch_curr_code = "060" then do; curr_name = "Bermudian Dollar"; curr_code = "BMD"; end;
else if tca_merch_curr_code = "064" then do; curr_name = "Ngultrum"; curr_code = "BTN"; end;
else if tca_merch_curr_code = "068" then do; curr_name = "Boliviano"; curr_code = "BOB"; end;
else if tca_merch_curr_code = "072" then do; curr_name = "Pula"; curr_code = "BWP"; end;
else if tca_merch_curr_code = "084" then do; curr_name = "Belize Dollar"; curr_code = "BZD"; end;
else if tca_merch_curr_code = "090" then do; curr_name = "Solomon Islands Dollar"; curr_code = "SBD"; end;
else if tca_merch_curr_code = "096" then do; curr_name = "Brunei Dollar"; curr_code = "BND"; end;
else if tca_merch_curr_code = "104" then do; curr_name = "Kyat"; curr_code = "MMK"; end;
else if tca_merch_curr_code = "108" then do; curr_name = "Burundi Franc"; curr_code = "BIF"; end;
else if tca_merch_curr_code = "116" then do; curr_name = "Riel"; curr_code = "KHR"; end;
else if tca_merch_curr_code = "124" then do; curr_name = "Canadian Dollar"; curr_code = "CAD"; end;
else if tca_merch_curr_code = "132" then do; curr_name = "Cabo Verde Escudo"; curr_code = "CVE"; end;
else if tca_merch_curr_code = "136" then do; curr_name = "Cayman Islands Dollar"; curr_code = "KYD"; end;
else if tca_merch_curr_code = "144" then do; curr_name = "Sri Lanka Rupee"; curr_code = "LKR"; end;
else if tca_merch_curr_code = "152" then do; curr_name = "Chilean Peso"; curr_code = "CLP"; end;
else if tca_merch_curr_code = "156" then do; curr_name = "Yuan Renminbi"; curr_code = "CNY"; end;
else if tca_merch_curr_code = "170" then do; curr_name = "Colombian Peso"; curr_code = "COP"; end;
else if tca_merch_curr_code = "174" then do; curr_name = "Comorian Franc"; curr_code = "KMF"; end;
else if tca_merch_curr_code = "188" then do; curr_name = "Costa Rican Colon"; curr_code = "CRC"; end;
else if tca_merch_curr_code = "191" then do; curr_name = "Kuna"; curr_code = "HRK"; end;
else if tca_merch_curr_code = "192" then do; curr_name = "Cuban Peso"; curr_code = "CUP"; end;
else if tca_merch_curr_code = "203" then do; curr_name = "Czech Koruna"; curr_code = "CZK"; end;
else if tca_merch_curr_code = "208" then do; curr_name = "Danish Krone"; curr_code = "DKK"; end;
else if tca_merch_curr_code = "214" then do; curr_name = "Dominican Peso"; curr_code = "DOP"; end;
else if tca_merch_curr_code = "222" then do; curr_name = "El Salvador Colon"; curr_code = "SVC"; end;
else if tca_merch_curr_code = "230" then do; curr_name = "Ethiopian Birr"; curr_code = "ETB"; end;
else if tca_merch_curr_code = "232" then do; curr_name = "Nakfa"; curr_code = "ERN"; end;
else if tca_merch_curr_code = "238" then do; curr_name = "Falkland Islands Pound"; curr_code = "FKP"; end;
else if tca_merch_curr_code = "242" then do; curr_name = "Fiji Dollar"; curr_code = "FJD"; end;
else if tca_merch_curr_code = "262" then do; curr_name = "Djibouti Franc"; curr_code = "DJF"; end;
else if tca_merch_curr_code = "270" then do; curr_name = "Dalasi"; curr_code = "GMD"; end;
else if tca_merch_curr_code = "292" then do; curr_name = "Gibraltar Pound"; curr_code = "GIP"; end;
else if tca_merch_curr_code = "320" then do; curr_name = "Quetzal"; curr_code = "GTQ"; end;
else if tca_merch_curr_code = "324" then do; curr_name = "Guinean Franc"; curr_code = "GNF"; end;
else if tca_merch_curr_code = "328" then do; curr_name = "Guyana Dollar"; curr_code = "GYD"; end;
else if tca_merch_curr_code = "332" then do; curr_name = "Gourde"; curr_code = "HTG"; end;
else if tca_merch_curr_code = "340" then do; curr_name = "Lempira"; curr_code = "HNL"; end;
else if tca_merch_curr_code = "344" then do; curr_name = "Hong Kong Dollar"; curr_code = "HKD"; end;
else if tca_merch_curr_code = "348" then do; curr_name = "Forint"; curr_code = "HUF"; end;
else if tca_merch_curr_code = "352" then do; curr_name = "Iceland Krona"; curr_code = "ISK"; end;
else if tca_merch_curr_code = "356" then do; curr_name = "Indian Rupee"; curr_code = "INR"; end;
else if tca_merch_curr_code = "360" then do; curr_name = "Rupiah"; curr_code = "IDR"; end;
else if tca_merch_curr_code = "364" then do; curr_name = "Iranian Rial"; curr_code = "IRR"; end;
else if tca_merch_curr_code = "368" then do; curr_name = "Iraqi Dinar"; curr_code = "IQD"; end;
else if tca_merch_curr_code = "376" then do; curr_name = "New Israeli Sheqel"; curr_code = "ILS"; end;
else if tca_merch_curr_code = "388" then do; curr_name = "Jamaican Dollar"; curr_code = "JMD"; end;
else if tca_merch_curr_code = "392" then do; curr_name = "Yen"; curr_code = "JPY"; end;
else if tca_merch_curr_code = "398" then do; curr_name = "Tenge"; curr_code = "KZT"; end;
else if tca_merch_curr_code = "400" then do; curr_name = "Jordanian Dinar"; curr_code = "JOD"; end;
else if tca_merch_curr_code = "404" then do; curr_name = "Kenyan Shilling"; curr_code = "KES"; end;
else if tca_merch_curr_code = "408" then do; curr_name = "North Korean Won"; curr_code = "KPW"; end;
else if tca_merch_curr_code = "410" then do; curr_name = "Won"; curr_code = "KRW"; end;
else if tca_merch_curr_code = "414" then do; curr_name = "Kuwaiti Dinar"; curr_code = "KWD"; end;
else if tca_merch_curr_code = "417" then do; curr_name = "Som"; curr_code = "KGS"; end;
else if tca_merch_curr_code = "418" then do; curr_name = "Lao Kip"; curr_code = "LAK"; end;
else if tca_merch_curr_code = "422" then do; curr_name = "Lebanese Pound"; curr_code = "LBP"; end;
else if tca_merch_curr_code = "426" then do; curr_name = "Loti"; curr_code = "LSL"; end;
else if tca_merch_curr_code = "430" then do; curr_name = "Liberian Dollar"; curr_code = "LRD"; end;
else if tca_merch_curr_code = "434" then do; curr_name = "Libyan Dinar"; curr_code = "LYD"; end;
else if tca_merch_curr_code = "446" then do; curr_name = "Pataca"; curr_code = "MOP"; end;
else if tca_merch_curr_code = "454" then do; curr_name = "Malawi Kwacha"; curr_code = "MWK"; end;
else if tca_merch_curr_code = "458" then do; curr_name = "Malaysian Ringgit"; curr_code = "MYR"; end;
else if tca_merch_curr_code = "462" then do; curr_name = "Rufiyaa"; curr_code = "MVR"; end;
else if tca_merch_curr_code = "480" then do; curr_name = "Mauritius Rupee"; curr_code = "MUR"; end;
else if tca_merch_curr_code = "484" then do; curr_name = "Mexican Peso"; curr_code = "MXN"; end;
else if tca_merch_curr_code = "496" then do; curr_name = "Tugrik"; curr_code = "MNT"; end;
else if tca_merch_curr_code = "498" then do; curr_name = "Moldovan Leu"; curr_code = "MDL"; end;
else if tca_merch_curr_code = "504" then do; curr_name = "Moroccan Dirham"; curr_code = "MAD"; end;
else if tca_merch_curr_code = "512" then do; curr_name = "Rial Omani"; curr_code = "OMR"; end;
else if tca_merch_curr_code = "516" then do; curr_name = "Namibia Dollar"; curr_code = "NAD"; end;
else if tca_merch_curr_code = "524" then do; curr_name = "Nepalese Rupee"; curr_code = "NPR"; end;
else if tca_merch_curr_code = "532" then do; curr_name = "Netherlands Antillean Guilder"; curr_code = "ANG"; end;
else if tca_merch_curr_code = "533" then do; curr_name = "Aruban Florin"; curr_code = "AWG"; end;
else if tca_merch_curr_code = "548" then do; curr_name = "Vatu"; curr_code = "VUV"; end;
else if tca_merch_curr_code = "554" then do; curr_name = "New Zealand Dollar"; curr_code = "NZD"; end;
else if tca_merch_curr_code = "558" then do; curr_name = "Cordoba Oro"; curr_code = "NIO"; end;
else if tca_merch_curr_code = "566" then do; curr_name = "Naira"; curr_code = "NGN"; end;
else if tca_merch_curr_code = "578" then do; curr_name = "Norwegian Krone"; curr_code = "NOK"; end;
else if tca_merch_curr_code = "586" then do; curr_name = "Pakistan Rupee"; curr_code = "PKR"; end;
else if tca_merch_curr_code = "590" then do; curr_name = "Balboa"; curr_code = "PAB"; end;
else if tca_merch_curr_code = "598" then do; curr_name = "Kina"; curr_code = "PGK"; end;
else if tca_merch_curr_code = "600" then do; curr_name = "Guarani"; curr_code = "PYG"; end;
else if tca_merch_curr_code = "604" then do; curr_name = "Sol"; curr_code = "PEN"; end;
else if tca_merch_curr_code = "608" then do; curr_name = "Philippine Peso"; curr_code = "PHP"; end;
else if tca_merch_curr_code = "634" then do; curr_name = "Qatari Rial"; curr_code = "QAR"; end;
else if tca_merch_curr_code = "643" then do; curr_name = "Russian Ruble"; curr_code = "RUB"; end;
else if tca_merch_curr_code = "646" then do; curr_name = "Rwanda Franc"; curr_code = "RWF"; end;
else if tca_merch_curr_code = "654" then do; curr_name = "Saint Helena Pound"; curr_code = "SHP"; end;
else if tca_merch_curr_code = "682" then do; curr_name = "Saudi Riyal"; curr_code = "SAR"; end;
else if tca_merch_curr_code = "690" then do; curr_name = "Seychelles Rupee"; curr_code = "SCR"; end;
else if tca_merch_curr_code = "694" then do; curr_name = "Leone"; curr_code = "SLL"; end;
else if tca_merch_curr_code = "702" then do; curr_name = "Singapore Dollar"; curr_code = "SGD"; end;
else if tca_merch_curr_code = "704" then do; curr_name = "Dong"; curr_code = "VND"; end;
else if tca_merch_curr_code = "706" then do; curr_name = "Somali Shilling"; curr_code = "SOS"; end;
else if tca_merch_curr_code = "710" then do; curr_name = "Rand"; curr_code = "ZAR"; end;
else if tca_merch_curr_code = "728" then do; curr_name = "South Sudanese Pound"; curr_code = "SSP"; end;
else if tca_merch_curr_code = "748" then do; curr_name = "Lilangeni"; curr_code = "SZL"; end;
else if tca_merch_curr_code = "752" then do; curr_name = "Swedish Krona"; curr_code = "SEK"; end;
else if tca_merch_curr_code = "756" then do; curr_name = "Swiss Franc"; curr_code = "CHF"; end;
else if tca_merch_curr_code = "760" then do; curr_name = "Syrian Pound"; curr_code = "SYP"; end;
else if tca_merch_curr_code = "764" then do; curr_name = "Baht"; curr_code = "THB"; end;
else if tca_merch_curr_code = "776" then do; curr_name = "Pa'anga"; curr_code = "TOP"; end;
else if tca_merch_curr_code = "780" then do; curr_name = "Trinidad and Tobago Dollar"; curr_code = "TTD"; end;
else if tca_merch_curr_code = "784" then do; curr_name = "UAE Dirham"; curr_code = "AED"; end;
else if tca_merch_curr_code = "788" then do; curr_name = "Tunisian Dinar"; curr_code = "TND"; end;
else if tca_merch_curr_code = "800" then do; curr_name = "Uganda Shilling"; curr_code = "UGX"; end;
else if tca_merch_curr_code = "807" then do; curr_name = "Denar"; curr_code = "MKD"; end;
else if tca_merch_curr_code = "818" then do; curr_name = "Egyptian Pound"; curr_code = "EGP"; end;
else if tca_merch_curr_code = "826" then do; curr_name = "Pound Sterling"; curr_code = "GBP"; end;
else if tca_merch_curr_code = "834" then do; curr_name = "Tanzanian Shilling"; curr_code = "TZS"; end;
else if tca_merch_curr_code = "840" then do; curr_name = "US Dollar"; curr_code = "USD"; end;
else if tca_merch_curr_code = "858" then do; curr_name = "Peso Uruguayo"; curr_code = "UYU"; end;
else if tca_merch_curr_code = "860" then do; curr_name = "Uzbekistan Sum"; curr_code = "UZS"; end;
else if tca_merch_curr_code = "882" then do; curr_name = "Tala"; curr_code = "WST"; end;
else if tca_merch_curr_code = "886" then do; curr_name = "Yemeni Rial"; curr_code = "YER"; end;
else if tca_merch_curr_code = "901" then do; curr_name = "New Taiwan Dollar"; curr_code = "TWD"; end;
else if tca_merch_curr_code = "927" then do; curr_name = "Unidad Previsional"; curr_code = "UYW"; end;
else if tca_merch_curr_code = "928" then do; curr_name = "Bolívar Soberano"; curr_code = "VES"; end;
else if tca_merch_curr_code = "929" then do; curr_name = "Ouguiya"; curr_code = "MRU"; end;
else if tca_merch_curr_code = "930" then do; curr_name = "Dobra"; curr_code = "STN"; end;
else if tca_merch_curr_code = "931" then do; curr_name = "Peso Convertible"; curr_code = "CUC"; end;
else if tca_merch_curr_code = "932" then do; curr_name = "Zimbabwe Dollar"; curr_code = "ZWL"; end;
else if tca_merch_curr_code = "933" then do; curr_name = "Belarusian Ruble"; curr_code = "BYN"; end;
else if tca_merch_curr_code = "934" then do; curr_name = "Turkmenistan New Manat"; curr_code = "TMT"; end;
else if tca_merch_curr_code = "936" then do; curr_name = "Ghana Cedi"; curr_code = "GHS"; end;
else if tca_merch_curr_code = "938" then do; curr_name = "Sudanese Pound"; curr_code = "SDG"; end;
else if tca_merch_curr_code = "940" then do; curr_name = "Uruguay Peso en Unidades Indexadas (UI)"; curr_code = "UYI"; end;
else if tca_merch_curr_code = "941" then do; curr_name = "Serbian Dinar"; curr_code = "RSD"; end;
else if tca_merch_curr_code = "943" then do; curr_name = "Mozambique Metical"; curr_code = "MZN"; end;
else if tca_merch_curr_code = "944" then do; curr_name = "Azerbaijan Manat"; curr_code = "AZN"; end;
else if tca_merch_curr_code = "946" then do; curr_name = "Romanian Leu"; curr_code = "RON"; end;
else if tca_merch_curr_code = "947" then do; curr_name = "WIR Euro"; curr_code = "CHE"; end;
else if tca_merch_curr_code = "948" then do; curr_name = "WIR Franc"; curr_code = "CHW"; end;
else if tca_merch_curr_code = "949" then do; curr_name = "Turkish Lira"; curr_code = "TRY"; end;
else if tca_merch_curr_code = "950" then do; curr_name = "CFA Franc BEAC"; curr_code = "XAF"; end;
else if tca_merch_curr_code = "951" then do; curr_name = "East Caribbean Dollar"; curr_code = "XCD"; end;
else if tca_merch_curr_code = "952" then do; curr_name = "CFA Franc BCEAO"; curr_code = "XOF"; end;
else if tca_merch_curr_code = "953" then do; curr_name = "CFP Franc"; curr_code = "XPF"; end;
else if tca_merch_curr_code = "955" then do; curr_name = "Bond Markets Unit European Composite Unit (EURCO)"; curr_code = "XBA"; end;
else if tca_merch_curr_code = "956" then do; curr_name = "Bond Markets Unit European Monetary Unit (E.M.U.-6)"; curr_code = "XBB"; end;
else if tca_merch_curr_code = "957" then do; curr_name = "Bond Markets Unit European Unit of Account 9 (E.U.A.-9)"; curr_code = "XBC"; end;
else if tca_merch_curr_code = "958" then do; curr_name = "Bond Markets Unit European Unit of Account 17 (E.U.A.-17)"; curr_code = "XBD"; end;
else if tca_merch_curr_code = "959" then do; curr_name = "Gold"; curr_code = "XAU"; end;
else if tca_merch_curr_code = "960" then do; curr_name = "SDR (Special Drawing Right)"; curr_code = "XDR"; end;
else if tca_merch_curr_code = "961" then do; curr_name = "Silver"; curr_code = "XAG"; end;
else if tca_merch_curr_code = "962" then do; curr_name = "Platinum"; curr_code = "XPT"; end;
else if tca_merch_curr_code = "963" then do; curr_name = "Codes specifically reserved for testing purposes"; curr_code = "XTS"; end;
else if tca_merch_curr_code = "964" then do; curr_name = "Palladium"; curr_code = "XPD"; end;
else if tca_merch_curr_code = "965" then do; curr_name = "ADB Unit of Account"; curr_code = "XUA"; end;
else if tca_merch_curr_code = "967" then do; curr_name = "Zambian Kwacha"; curr_code = "ZMW"; end;
else if tca_merch_curr_code = "968" then do; curr_name = "Surinam Dollar"; curr_code = "SRD"; end;
else if tca_merch_curr_code = "969" then do; curr_name = "Malagasy Ariary"; curr_code = "MGA"; end;
else if tca_merch_curr_code = "970" then do; curr_name = "Unidad de Valor Real"; curr_code = "COU"; end;
else if tca_merch_curr_code = "971" then do; curr_name = "Afghani"; curr_code = "AFN"; end;
else if tca_merch_curr_code = "972" then do; curr_name = "Somoni"; curr_code = "TJS"; end;
else if tca_merch_curr_code = "973" then do; curr_name = "Kwanza"; curr_code = "AOA"; end;
else if tca_merch_curr_code = "975" then do; curr_name = "Bulgarian Lev"; curr_code = "BGN"; end;
else if tca_merch_curr_code = "976" then do; curr_name = "Congolese Franc"; curr_code = "CDF"; end;
else if tca_merch_curr_code = "977" then do; curr_name = "Convertible Mark"; curr_code = "BAM"; end;
else if tca_merch_curr_code = "978" then do; curr_name = "Euro"; curr_code = "EUR"; end;
else if tca_merch_curr_code = "979" then do; curr_name = "Mexican Unidad de Inversion (UDI)"; curr_code = "MXV"; end;
else if tca_merch_curr_code = "980" then do; curr_name = "Hryvnia"; curr_code = "UAH"; end;
else if tca_merch_curr_code = "981" then do; curr_name = "Lari"; curr_code = "GEL"; end;
else if tca_merch_curr_code = "984" then do; curr_name = "Mvdol"; curr_code = "BOV"; end;
else if tca_merch_curr_code = "985" then do; curr_name = "Zloty"; curr_code = "PLN"; end;
else if tca_merch_curr_code = "986" then do; curr_name = "Brazilian Real"; curr_code = "BRL"; end;
else if tca_merch_curr_code = "990" then do; curr_name = "Unidad de Fomento"; curr_code = "CLF"; end;
else if tca_merch_curr_code = "994" then do; curr_name = "Sucre"; curr_code = "XSU"; end;
else if tca_merch_curr_code = "997" then do; curr_name = "US Dollar (Next day)"; curr_code = "USN"; end;
if smh_multi_org_node_key = 'AAAABC' then CPP = rur_4byte_string_001;
else CPP = rob_cust_ind;
Name = cat(rua_20byte_string_005,rua_8byte_string_002);
Amount_Band = int(tca_mod_amt/100)*100;
Score_Band = int(rrr_score_1/50)*50;
If CMX_USER_FRAUD_C_IND = '1' or RHX_SCORE_1_SAS_MATCH_IND in ('01','02','03') then
Fraud = 'Y'; else
Fraud = 'N';
DOB = input(substr(left(put(rua_numeric_042,best.)),1,10),yymmdd10.);
Age = int(datdif(DOB, rhx_date_key, 'act/act')/365);
Age_Band = int(Age/5)*5;
Issue = input(substr(left(put(rua_numeric_002,best.)),1,10),yymmdd10.); format Issue date9.;
Open = input(substr(left(put(rua_numeric_002,best.)),1,10),yymmdd10.); format Open date9.;
PIN_Issue = input(substr(left(put(rua_numeric_010,best.)),1,10),yymmdd10.); format PIN_Issue date9.;
PIN_Days = rhx_date_key - PIN_Issue;
Date_Time = dhms(rqo_tran_date_alt,0,0,RQO_TRAN_TIME_ALT);
Arcot_Time_Diff = Date_Time - _c_arcot_dt;
Hour = hour(rqo_tran_time_alt);
Day = weekday(rhx_date_key);
Days_issued = rhx_date_key - Issue ;
Merch_Days = rhx_date_key - datepart(_m_first_seen);
Prev_mer_2_seconds = _c_prev_mer_dt_1 - _c_prev_mer_dt_2;
Prev_mer_2_minutes = int((_c_prev_mer_dt_1 - _c_prev_mer_dt_2)/60);
if hct_term_cntry_code = '840' then US_State = zipstate(substr( hct_term_post_code,1,5));
if hct_term_cntry_code = '826' AND substr(hqo_card_num,1,1) = '4' then hct_term_post_code = _m_merchant_post_code;
if rur_ind_001 in ('0', '1','6') then Approved = 'Y';
else Approved = 'N';
if rob_action_code = '6' and rob_action_until_date >= rhx_date_key then Anti = 'Y';
else if rob_action_code = '3' and rob_action_until_date >= rhx_date_key then Anti = 'R';
else Anti = 'N';
prev_spend = int((_c_prev_mer_vel_cnt/100))/100;
prev_count = mod(_c_prev_mer_vel_cnt,100);
speed = _c_prev_mer_dt_1 - _c_prev_mer_vel_fdt;
Exclude_Date = int(_c_exclude_count/100);
Exclude_Count = mod(_c_exclude_count,100);
Token_dt = int(_c_token_dt/100);
Token_Date = datepart(Token_dt); format Token_Date date9.;
add_change_date = int(_a_address_date_distance/10000); format add_change_date date9.;
offline_Count = mod(_c_atc_offline_auth_today,1000);
open_date = rua_numeric_039; format open_date date9.;
debitanti = Rua_2byte_string_001; /* 9 anti, 7 above anti */
RUR_8BYTE_STRING_001 = exclusion_flag;
prev_pos = substr(_c_prev_mer_ext_2,11,2);
prev_pos2 = substr(_c_prev_mer_ext_3,11,2);
prev_pos3 = substr(_c_prev_mer_ext_4,11,2);
prev_mcc1 = substr(_c_prev_mer_ext_2,1,4);
prev_mcc2 = substr(_c_prev_mer_ext_3,1,4);
prev_mcc3 = substr(_c_prev_mer_ext_4,1,4);
run;
data sample
(
keep=
cmx_tran_id hqo_card_num smh_multi_org_node_key smh_multi_org_name Name rhx_date_key
rhx_time_key hct_mer_mcc hct_term_owner_name hct_term_owner_id merch_country
tca_mod_amt tca_merch_curr_code curr_code Fraud Approved Anti
RUR_8BYTE_STRING_001 rrr_score_1 score_band rob_cust_ind ucm_avs_resp
UCM_POS hct_term_city hct_mer_mcc DOB age age_band rrr_action_code curr_name
hct_term_cntry_code amount_band open_date Issue Debitanti exclusion_flag
);
retain
cmx_tran_id
hqo_card_num
smh_multi_org_node_key
smh_multi_org_name
Name
open_date
rhx_date_key
rhx_time_key
hct_mer_mcc
hct_term_owner_name
hct_term_owner_id
merch_country
tca_mod_amt
tca_merch_curr_code
curr_code
Fraud
Approved
Anti
Debitanti
exclusion_flag
RUR_8BYTE_STRING_001
rrr_score_1
score_band
rob_cust_ind
ucm_avs_resp
UCM_POS
hct_term_city
hct_mer_mcc
DOB
age
age_band
rrr_action_code
curr_name
hct_term_cntry_code
amount_band
Issue
;
set extract_vars;
run;
data frauds;
set sample;
where fraud = 'Y'
;
run;
proc freq data=sample;
tables fraud hct_term_owner_name*fraud smh_multi_org_name*fraud ucm_pos*fraud score_band*fraud amount_band*fraud age_band*fraud tca_merch_curr_code*fraud curr_name*fraud / NOROW NOCOL NOPERCENT MISSPRINT NOCUM;
run;
proc freq data=frauds;
tables fraud hct_term_owner_name*fraud smh_multi_org_name*fraud ucm_pos*fraud score_band*fraud amount_band*fraud age_band*fraud tca_merch_curr_code*fraud curr_name*fraud / NOROW NOCOL NOPERCENT MISSPRINT NOCUM;
run;
data sample_narrow;
set sample;
where hct_term_owner_id = 'XXX'
;run;
data ruletest;
set sample_narrow;
where
rrr_score_1 > 500
and tca_mod_amt >= 2000
;
run;
