topic Re: How to skip records that do not have entry for the current month scenario execution in SAS Programming

How to skip records that do not have entry for the current month scenario execution

Ololade — Fri, 19 Jun 2020 17:30:06 GMT

Hi All,

Kindly assist.

I have a dataset that has monthly transaction records of customers. I need to input a code in my scenario to skip customers that do not have entry for the current month scenario execution.

Here is my code:

FORMAT rundate_number p10601_end_date 8.;

/*We created 1 variables, p10601_end_date, in order to check the date_key */
rundate_number=input(put(rundate,date_to_num.),8.);
p10601_end_date = input(put(rundate_number,num_to_date.),8.);

/* Skip parties that do not have transactions on the current day */
if month_key{n} gt p10601_end_date then goto &sysmacroname.;

Thank you.

Re: How to skip records that do not have entry for the current month scenario execution

smantha — Fri, 19 Jun 2020 17:58:33 GMT

First of all more code and data samples would be handy. I do not see a need for go to unless you want to do some logic and return back to the main code. If your sole objective is to skip records that do not match a certain criteria and you do not want them in the output you can just add the following line the code

if some_date = current_date;

The lines following that conditional statement including output will only be executed if the conditional is true.

Re: How to skip records that do not have entry for the current month scenario execution

Patrick — Sat, 20 Jun 2020 07:16:37 GMT

That looks like a SAS AML transaction monitoring scenario.

It's too long ago that I've actually done something with AML so below just some pseudo-code to give you the idea.

I believe what you need to do is to create a look-up table with all customers that have had a transaction on the day. In below code that's done via a hash table (syntax needs amendment with actual column names; also make sure that "&runasofdate" matches the data type of "transaction_date").

"PrepFile" in this bit dcl hash h_p10601(dataset:"PrepFile(keep=party_no... needs to be the name of your header/prep/abt file ...I believe that's also stored in some macro variable which you could use.

You then can check in your scenario logic if a customer exists in the lookup table (the hash table) and skip the execution if it doesn't - no more sure whether goto &sysmacroname. is what makes the scenario execute or not for a specific row; in below logic it should exclude the transaction/customer if condition h_p10601.check() ne 0 is TRUE.

FORMAT rundate_number p10601_end_date 8.;

/*We created 1 variables, p10601_end_date, in order to check the date_key */
rundate_number=input(put(rundate,date_to_num.),8.);
p10601_end_date = input(put(rundate_number,num_to_date.),8.);

if _n_=1 then
  do;
    dcl hash h_p10601(dataset:"PrepFile(keep=party_no transaction_date (where=(transaction_date='&runasofdate'd)))");
    h_p10601.defineKey('party_no');
    h_p10601.defineDone();
  end;

/* Skip parties that do not have transactions on the current day */
if h_p10601.check() ne 0 then goto &sysmacroname.;

....actually: I'm even no more sure if the header bit now got all the transactions for a customer transposed into a single row or not. If it's a single row then above code wouldn't work and you would need to search through the array to figure out if there is a transaction for the &runasofdate (and don't use the execution/system date but the date for which the AGP executes - I believe to remember that this is &runasofdate - but eventually that was also only something site specific we did ...too long ago!).

Can you please share the header code section and eventually your scenario code as well. ...if the header is auto-generated then you should still be able to extract the code from the SAS log.

Re: How to skip records that do not have entry for the current month scenario execution

Ololade — Tue, 23 Jun 2020 16:38:21 GMT

Hello Patrick,

Thank you for your input.

Attached is the scenario code. I tried attaching the header code but it keeps giving an error, so here it is:

%let header_id = 68634;

/* Set error code to 0 */
%let trans_rc = 0;

/* Include Scenario Messages */
filename scenMsgs "!AMLROOT/scenario/codegen/scenario_messages.sas" encoding='windows';
%include scenMsgs;

/* Set alert filenames */
%let alert_fname=work._tempalerts;
%let alert_fname_final=STG_ALER.CUSTOM_ACCOUNT_ALERTS&runasofdate ;

/* Remove Previous Temporary Alert Table */
proc datasets lib=work nowarn nolist;
delete _tempalerts;
quit;
%fcf_rcset(&syserr);

/* Macros for each scenario/risk factor */
/* 10076 */
%macro fcf_run_scenario_391674_1 (
p10076_account_type_desc=,
p10076_amt_pct_increase=,
p10076_cnt_pct_increase=,
p10076_corr_seg=,
p10076_curr_amt_seg1_high=,
p10076_curr_amt_seg1_low=,
p10076_curr_amt_seg1_med=,
p10076_curr_amt_seg1_veryhigh=,
p10076_curr_amt_seg2_high=,
p10076_curr_amt_seg2_low=,
p10076_curr_amt_seg2_med=,
p10076_curr_amt_seg2_veryhigh=,
p10076_curr_amt_seg3_high=,
p10076_curr_amt_seg3_low=,
p10076_curr_amt_seg3_med=,
p10076_curr_amt_seg3_veryhigh=,
p10076_curr_amt_seg4_high=,
p10076_curr_amt_seg4_low=,
p10076_curr_amt_seg4_med=,
p10076_curr_amt_seg4_veryhigh=,
p10076_curr_amt_seg5_high=,
p10076_curr_amt_seg5_low=,
p10076_curr_amt_seg5_med=,
p10076_curr_amt_seg5_veryhigh=,
p10076_curr_count=,
p10076_high=,
p10076_low=,
p10076_med=,
p10076_N_exclude_swift=,
p10076_num_month=,
p10076_seg1=,
p10076_seg2=,
p10076_seg3=,
p10076_seg4=,
p10076_seg5=,
p10076_veryhigh=
);

%include 'E:\SAS\Config\Lev1\Applications\SASComplianceSolutions/FCFBU1/scenario/scenario_code_active/CON10076_v_19.sas' ;
%mend;

/* End scenario/risk factor macros */

/* Set array_dimension value */
%fcf_calculate_array_dim(ds=MST_PREP.ACCOUNT_PROFILE_CUSTOM, nkey=ACCOUNT_NUMBER);
%fcf_rcset(&syserr);

/* Begin Header Code */

data &alert_fname
;
if 0 then set seg_kc.fsk_alert(keep=actual_values_text primary_entity_number primary_entity_level_code rename=(primary_entity_level_code=entity_level_code));
keep primary_entity_number scenario_id rundate actual_values_text entity_level_code
entity_segment_id
;
format rundate date9. ;
array ACCOUNT_TYPE_DESC (&array_dimension) $1 _temporary_ ;
array ACCOUNT_VALUE_AMOUNT (&array_dimension) $1 _temporary_ ;
array AVERAGE_DAILY_BALANCE_AMOUNT (&array_dimension) $1 _temporary_ ;
array AVG_ACCOUNT_VALUE_AMOUNT (&array_dimension) $1 _temporary_ ;
array BEGINNING_BALANCE_AMOUNT (&array_dimension) $1 _temporary_ ;
array CASH_TRANSACTIONS_COUNT (&array_dimension) $1 _temporary_ ;
array CLOSING_BALANCE_AMOUNT (&array_dimension) $1 _temporary_ ;
array COUNT_CREDIT_CASH (&array_dimension) $1 _temporary_ ;
array COUNT_CREDIT_WIRE (&array_dimension) _temporary_ ;
array COUNT_DEBIT_CASH (&array_dimension) $1 _temporary_ ;
array COUNT_DEBIT_WIRE (&array_dimension) _temporary_ ;
array CUST_INITIATED_TRANS_COUNT (&array_dimension) $1 _temporary_ ;
array CUSTOMER_SEGMENT (&array_dimension) $1 _temporary_ ;
array DEPOSITS_COUNT (&array_dimension) $1 _temporary_ ;
array ENDING_CASH_BALANCE_AMOUNT (&array_dimension) $1 _temporary_ ;
array LARGEST_CREDIT_AMOUNT (&array_dimension) _temporary_ ;
array LARGEST_DEBIT_AMOUNT (&array_dimension) _temporary_ ;
array MAX_ACCOUNT_VALUE_AMOUNT (&array_dimension) $1 _temporary_ ;
array MIN_ACCOUNT_VALUE_AMOUNT (&array_dimension) $1 _temporary_ ;
array MONTH_KEY (&array_dimension) _temporary_ ;
array RISK_LEVEL (&array_dimension) _temporary_ ;
array TOTAL_CASH_CREDITS_AMOUNT (&array_dimension) $1 _temporary_ ;
array TOTAL_CASH_DEBITS_AMOUNT (&array_dimension) $1 _temporary_ ;
array TOTAL_CREDITS_AMOUNT (&array_dimension) _temporary_ ;
array TOTAL_DEBITS_AMOUNT (&array_dimension) _temporary_ ;
array TOTAL_TRANSACTIONS_AMOUNT (&array_dimension) _temporary_ ;
array TOTAL_WIRE_CREDITS_AMOUNT (&array_dimension) _temporary_ ;
array TOTAL_WIRE_DEBITS_AMOUNT (&array_dimension) _temporary_ ;
array TRANSACTIONS_COUNT (&array_dimension) _temporary_ ;
array WIRES_COUNT (&array_dimension) _temporary_ ;
array WITHDRAWALS_COUNT (&array_dimension) $1 _temporary_ ;
set MST_PREP.ACCOUNT_PROFILE_CUSTOM
(rename=(
ACCOUNT_TYPE_DESC = temp2
ACCOUNT_VALUE_AMOUNT = temp3
AVERAGE_DAILY_BALANCE_AMOUNT = temp4
AVG_ACCOUNT_VALUE_AMOUNT = temp5
BEGINNING_BALANCE_AMOUNT = temp6
CASH_TRANSACTIONS_COUNT = temp7
CLOSING_BALANCE_AMOUNT = temp8
COUNT_CREDIT_CASH = temp9
COUNT_CREDIT_WIRE = temp10
COUNT_DEBIT_CASH = temp11
COUNT_DEBIT_WIRE = temp12
CUST_INITIATED_TRANS_COUNT = temp13
CUSTOMER_SEGMENT = temp14
DEPOSITS_COUNT = temp15
ENDING_CASH_BALANCE_AMOUNT = temp16
LARGEST_CREDIT_AMOUNT = temp18
LARGEST_DEBIT_AMOUNT = temp19
MAX_ACCOUNT_VALUE_AMOUNT = temp20
MIN_ACCOUNT_VALUE_AMOUNT = temp21
MONTH_KEY = temp22
RISK_LEVEL = temp23
TOTAL_CASH_CREDITS_AMOUNT = temp24
TOTAL_CASH_DEBITS_AMOUNT = temp25
TOTAL_CREDITS_AMOUNT = temp26
TOTAL_DEBITS_AMOUNT = temp27
TOTAL_TRANSACTIONS_AMOUNT = temp28
TOTAL_WIRE_CREDITS_AMOUNT = temp29
TOTAL_WIRE_DEBITS_AMOUNT = temp30
TRANSACTIONS_COUNT = temp31
WIRES_COUNT = temp32
WITHDRAWALS_COUNT = temp33
))
;
by ACCOUNT_NUMBER ;
if _n_ eq 1 then do;
retain rundate rundate_number entity_level_code run_scenario_init;
rundate = input(put(&runasofdate,8.),yymmdd8.);
rundate_number = input(put(rundate,date_to_num.),8.);
entity_level_code = "ACC";
run_scenario_init = 1;
end;
if first.ACCOUNT_NUMBER then n = 0;
n + 1;
if not entity_segment_id then entity_segment_id=-1;
if n le &array_dimension then do;
ACCOUNT_TYPE_DESC{n} = temp2;
ACCOUNT_VALUE_AMOUNT{n} = temp3;
AVERAGE_DAILY_BALANCE_AMOUNT{n} = temp4;
AVG_ACCOUNT_VALUE_AMOUNT{n} = temp5;
BEGINNING_BALANCE_AMOUNT{n} = temp6;
CASH_TRANSACTIONS_COUNT{n} = temp7;
CLOSING_BALANCE_AMOUNT{n} = temp8;
COUNT_CREDIT_CASH{n} = temp9;
COUNT_CREDIT_WIRE{n} = temp10;
COUNT_DEBIT_CASH{n} = temp11;
COUNT_DEBIT_WIRE{n} = temp12;
CUST_INITIATED_TRANS_COUNT{n} = temp13;
CUSTOMER_SEGMENT{n} = temp14;
DEPOSITS_COUNT{n} = temp15;
ENDING_CASH_BALANCE_AMOUNT{n} = temp16;
LARGEST_CREDIT_AMOUNT{n} = temp18;
LARGEST_DEBIT_AMOUNT{n} = temp19;
MAX_ACCOUNT_VALUE_AMOUNT{n} = temp20;
MIN_ACCOUNT_VALUE_AMOUNT{n} = temp21;
MONTH_KEY{n} = temp22;
RISK_LEVEL{n} = temp23;
TOTAL_CASH_CREDITS_AMOUNT{n} = temp24;
TOTAL_CASH_DEBITS_AMOUNT{n} = temp25;
TOTAL_CREDITS_AMOUNT{n} = temp26;
TOTAL_DEBITS_AMOUNT{n} = temp27;
TOTAL_TRANSACTIONS_AMOUNT{n} = temp28;
TOTAL_WIRE_CREDITS_AMOUNT{n} = temp29;
TOTAL_WIRE_DEBITS_AMOUNT{n} = temp30;
TRANSACTIONS_COUNT{n} = temp31;
WIRES_COUNT{n} = temp32;
WITHDRAWALS_COUNT{n} = temp33;
end;
if not last.ACCOUNT_NUMBER then return;

/* End Header Code Generation */

/* Call each scenario/risk factor macro after setting scenario_id variable.*/
scenario_id = 391674;
if entity_segment_id = -1 or (
-1 = -1 /* is this the call for default */
and entity_segment_id not in ( /* is the segment for this entity specified, or do we call default */
-1
)
) then do;
/* CON10076 */
entity_level_code = "ACC";
primary_entity_number=ACCOUNT_NUMBER;
%fcf_run_scenario_391674_1 (
p10076_account_type_desc=%str('P','C') ,
p10076_amt_pct_increase=20 ,
p10076_cnt_pct_increase=20 ,
p10076_corr_seg=%str(('Z','R')) ,
p10076_curr_amt_seg1_high=1000 ,
p10076_curr_amt_seg1_low=1000 ,
p10076_curr_amt_seg1_med=1000 ,
p10076_curr_amt_seg1_veryhigh=1000 ,
p10076_curr_amt_seg2_high=1000 ,
p10076_curr_amt_seg2_low=1000 ,
p10076_curr_amt_seg2_med=1000 ,
p10076_curr_amt_seg2_veryhigh=1000 ,
p10076_curr_amt_seg3_high=1000 ,
p10076_curr_amt_seg3_low=1000 ,
p10076_curr_amt_seg3_med=1000 ,
p10076_curr_amt_seg3_veryhigh=1000 ,
p10076_curr_amt_seg4_high=1000 ,
p10076_curr_amt_seg4_low=1000 ,
p10076_curr_amt_seg4_med=1000 ,
p10076_curr_amt_seg4_veryhigh=1000 ,
p10076_curr_amt_seg5_high=1000 ,
p10076_curr_amt_seg5_low=1000 ,
p10076_curr_amt_seg5_med=1000 ,
p10076_curr_amt_seg5_veryhigh=1000 ,
p10076_curr_count=5 ,
p10076_high=3 ,
p10076_low=1 ,
p10076_med=2 ,
p10076_N_exclude_swift=%str('CITIUS33','CITIGB2L','SCBLGB2L','SCBLDEFX','NWBKGB2L') ,
p10076_num_month=5 ,
p10076_seg1=%str('I') ,
p10076_seg2=%str('J') ,
p10076_seg3=%str('E') ,
p10076_seg4=%str('C') ,
p10076_seg5=%str('R') ,
p10076_veryhigh=4
);
end; /* if segment_ids == */

run_scenario_init = 0;
drop run_scenario_init;
run;

%fcf_rcset(&syserr);

/* Append results to final alert file */
proc append data=&alert_fname
base=&alert_fname_final FORCE;
run;
%fcf_rcset(&syserr);

Re: How to skip records that do not have entry for the current month scenario execution

Ololade — Tue, 23 Jun 2020 16:39:14 GMT

Hi Smantha,

I tried this but it didn't work.

Re: How to skip records that do not have entry for the current month scenario execution

Patrick — Wed, 24 Jun 2020 07:12:08 GMT

That's an account based scenario and you've got one row per account (and then arrays with monthly account profile data).

Now you want to exclude accounts from customers that didn't have a transaction in the current month (or last 30 days; or whatever you choose).

Transactions are between accounts and not customers but a customer can own multiple accounts - and you've got primary and secondary account holders.

First thing you need to decide: Do you want to exclude an account with no current transactions if the owning customer has another account that had transactions?

Whatever way you want to go: You currently don't have the necessary data in the Prep file you're using. What you need to construct is a lookup table with account numbers to include (or exclude) from monitoring. To get there you will first have to create a distinct list of account numbers from the transaction table (=all account numbers where transaction_date=&runasofdate). If you're excluding account based then you've got your lookup table (and you could do the lookup via a hash table as proposed earlier).

If you want to only exclude accounts if the owning customer didn't have any transaction on any of the other accounts owned by the customer then you would need to lookup the customer via the Party_Account bridge, select all matching customers and then lookup all accounts they own again via the Party_Account Bridge - and then use this list of distinct accounts as lookup table.

To create the lookup table: The scenarios execute within a SAS data step so that's nothing you can implement within the scenario code. You basically need to create another daily prep file (lookup table) and you then load this prep file into a hash table as part of your scenario code and then do the lookup.

Does this make sense?

Re: How to skip records that do not have entry for the current month scenario execution

Ololade — Wed, 24 Jun 2020 06:55:37 GMT

Thank you Patrick.
I'll try the lookup table approach to exclude accounts with no profile entry for the rundate.

Best regards and stay safe.
Ololade