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;
@Chris_T wrote:
Hi ballardw,
Thank you for your comments in regards to countries 3 character code.
What I am trying to achieve as a result is for the age_band (25 to 70) in my data set to identify the number of genuine and the number of fraudulent transactions from a particular merchant id (hct_term_owner_id= ' ') for rhx_date_key>= (a particular date) and then maybe add rrr_score_1 > . . . and tca_mod_amt > . . .
Can you assist me with the necessary coding needed?
Kind regards
Chris
You have so much code that is not related to identify genuine or fraudulent claims that I have no idea how to do that from the code shown. Reduce the problem and code to a smaller number of variables and records. Basically just enough to identify a record and the specific information needed to identify fraudulent. Then describe the rules for what is fraudulent.
You comment on "age band" isn't clear whether you want a single age band (20-75) or 5-year wide bands as implied by your ageband coding.
Provide some dummy data values that you can use demonstrate the values with some that would be fraudulent and others genuine.
Assignments like this
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"
are a clear case for creating a format that translates the values; it might be even the case you do not need a new variable, but simply display hct_term_cntry_code with the format when the long name is needed in a report. Reduces storage usage quite nicely 😉
If you assign the format to the variable, you'll even see the long names in the viewtable window.
The many case-when in your initial SQL are better done in a data step, where you can set several variables in one if/then do/end block.
To give us an idea what you want to do, prepare some example data and post it in a data step with datalines, and what you want to get out of that. Without seeing this, it's very hard to tell what you want to do.
Hi Curt,
Don't extract sensitive data. Make up some.
My take from your description is to create a table that essentially contains this:
data transaction;
input fraud $ age_band;
datalines;
Y 1
Y 1
N 1
N 1
N 1
N 2
N 3
Y 2
Y 4
;
and then run a proc freq on it:
proc freq data=transaction;
tables age_band * fraud;
run;
Hi Kurt,
Not sure I understand the interpretation of the table you are suggesting.
data transaction;
input fraud $ age_band;
datalines;Y 1
Y 1
N 1
N 1
N 1
N 2
N 3
Y 2
Y 4;
I need to create a table where it indicates the number of genuine and fraudulent transactions by age_band when I use as a filter a single hct_term_owner_id (can I have the table to indicate the number of genuine and fraudulent transactions for more than just one hct_term_owner_id?
Thank you in advance for your prompt reply.
Kind regards,
Chris
It's really close to impossible to give you an answer just based on all the code you've posted especially if your problem also lies in the logic not being how you need it.
Try to boil down the issue to something as simple as possible, then post made up sample data in the form of a SAS data step creating this data, explain us the logic you need and show us the desired result based on the sample data you've posted.
Looking at the code you've posted initially:
- It feels like there must be a simpler way than to create all these variables with all these case statements.
- Use a Format instead of all these IF/THEN/ELSE statements to populate merch-country. This will make your code much more readable and easier to maintain.
proc format;
value $codeTocountry(default=50)
'004'="Afghanistan"
'008'="Albania"
.....
;
run;
data extract_vars;
....
merch_country=put(hct_term_cntry_code,$codeTocountry.);
....
run;
Hi Patrick,
Thank you for your thoughtful comments.
Unfortunately, I cannot extract data from my original data set (sensitive data)
What I am trying to achieve as a result is for the age_band (25 to 70) in my data set to identify the number of genuine and the number of fraudulent transactions from a particular merchant id (hct_term_owner_id= ' ') for rhx_date_key>= (a particular date) and then maybe add rrr_score_1 > . . . and tca_mod_amt > . . .
Kind regards
Chris
No one expects you to post real data but you can always post made-up data.
So where do you get stuck? Looks like you can already identify fraud as such.
If CMX_USER_FRAUD_C_IND = '1' or RHX_SCORE_1_SAS_MATCH_IND in ('01','02','03') then Fraud = 'Y'; else Fraud = 'N';
@Chris_T wrote:
Hi Kurt,
Not sure I understand the interpretation of the table you are suggesting.
data transaction; input fraud $ age_band; datalines;Y 1 Y 1 N 1 N 1 N 1 N 2 N 3 Y 2 Y 4;
I need to create a table where it indicates the number of genuine and fraudulent transactions by age_band when I use as a filter a single hct_term_owner_id (can I have the table to indicate the number of genuine and fraudulent transactions for more than just one hct_term_owner_id?
Thank you in advance for your prompt reply.
Kind regards,
Chris
Filtering is done with a where condition. In addition, you can sort your data and use by in proc freq to get separate tables.
Does your SAS install have the library MAPSGFK supplied?
If so you can save yourself a lot of work by looking at MAPSGFK.WORLD_ATT.
It has a 3 character code variable ISO that looks a lot like your hct_term_cntry_code variable values, an IDNAME with value of 'Afghanistan' for ISO='004', plus and ISOALPHA3 of 'AFG' for the same.
So you could likely either use that data set to create formats or join the data set and bring thos evalues
@Chris_T wrote:
Hi ballardw,
Thank you for your comments in regards to countries 3 character code.
What I am trying to achieve as a result is for the age_band (25 to 70) in my data set to identify the number of genuine and the number of fraudulent transactions from a particular merchant id (hct_term_owner_id= ' ') for rhx_date_key>= (a particular date) and then maybe add rrr_score_1 > . . . and tca_mod_amt > . . .
Can you assist me with the necessary coding needed?
Kind regards
Chris
You have so much code that is not related to identify genuine or fraudulent claims that I have no idea how to do that from the code shown. Reduce the problem and code to a smaller number of variables and records. Basically just enough to identify a record and the specific information needed to identify fraudulent. Then describe the rules for what is fraudulent.
You comment on "age band" isn't clear whether you want a single age band (20-75) or 5-year wide bands as implied by your ageband coding.
Provide some dummy data values that you can use demonstrate the values with some that would be fraudulent and others genuine.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.