topic Re: Sas aml scenario in SAS Programming

Sas aml scenario

rajusas — Fri, 16 Mar 2018 22:54:10 GMT

Hi
I have 6month transaction data
I want to get sum of currency amount on each month wise is greater than some value
If condition meets I need count on month wise

Please help with code

Re: Sas aml scenario

Patrick — Fri, 16 Mar 2018 23:15:21 GMT

https://communities.sas.com/t5/Getting-Started/How-to-get-fast-helpful-answers/ta-p/226133

Re: Sas aml scenario

Reeza — Sat, 17 Mar 2018 01:26:41 GMT

@rajusas wrote:
Hi
I have 6month transaction data
I want to get sum of currency amount on each month wise is greater than some value
If condition meets I need count on month wise

Please help with code

If you want code you need to include:

1. Sample data

2. Example of calculation required using #1

3. Preferably what you've tried as well so we can point out what you need to change.

Re: Sas aml scenario

rajusas — Sat, 17 Mar 2018 04:25:37 GMT

Data
Datekey currencyamount ACCnum
16mar2018 1000 123
16mar2018 200 123
16mar2018 300 124
15mar 2018 500 125
15mar2018 400 126
,
,
,
,
,
upto Previous 6months
I need sum (curr_amoun)t is between 500 to 900 in one month(twice in 6
months)

Now this is my code
Begindate=input(put(rundate-180+1,num_to_date.),8.);
Do i=n to 1 by -1 while(datekey {i}>=begindate)
if begindate period_date= input(put( rundate-30+1, num_to_date.),8.);

Do i=n to 1 by -1 while(datekey {i}>=begindate)
if period_date
sum=0;
count=0;
sum=sum(sum,currencyamount{i});
if 500<900 then count+1;
end;
end;
end;
end;

if count>=2 then alert;

Re: Sas aml scenario

Patrick — Sat, 17 Mar 2018 07:26:22 GMT

@rajusas

Are you trying to write a rule which works as part of the SAS AML solution?

In regards of sample data: What you should provide is a fully working SAS data step which creates sample data - AND the sample data should have sufficient records for the rule you're after to actually fire.

And for your rule: When it comes to transaction monitoring then you're normally looking at some thresholds or patterns within a rolling time window and not at month by month. Once you've provided us with useful sample data you might also want to clearly articulate the rule you're after. ...and what's also important to provide you with something useful: What volumes are you dealing with and how is your environment sized (especially how much memory do you have available)?

Re: Sas aml scenario

rajusas — Sat, 17 Mar 2018 08:02:33 GMT

Thanks for your reply.

data sample;

infile datalines;

input date_key date9. currency_amount account_number;

datalines;

17mar2018 1000 111

17mar2018 2000 111

16mar2018 1200 111

15 mar 2018 1000 111
14 mar 2018 1500 111

13 mar 2018 1800 111

12mar2018 1600 111

11 mar 2018 2200 111

10 mar 2018 2200 111

09 mar 2018 2200 111

08 mar 2018 2200 111

07 mar 2018 2200 111

;

from above 10 days data

i want every 5 days total transaction amount is greaterthan 4000 and

if this kind of transaction occurs in 2 times in 10 days get alert

Re: Sas aml scenario

rajusas — Thu, 22 Mar 2018 09:06:02 GMT

Hi Patrick
We have 15 lakh observations for one day.
Please reply on above question

Re: Sas aml scenario

Patrick — Thu, 22 Mar 2018 09:47:09 GMT

@rajusas

Does this need to be syntax which will be working within the SAS AML Solution....

http://support.sas.com/software/products/aml/index.html

.... or is this unrelated to the SAS solution and you can do whatever you want?

Please answer this question first as it's essential for how to address what you're after.

As for your rule:

"from above 10 days data

i want every 5 days total transaction amount is greaterthan 4000 and

if this kind of transaction occurs in 2 times in 10 days get alert"

Normally AML transaction monitoring rules are run at least daily (and some like credit cards real time) including all transactions for a defined time period and eventually also including profile information from a much longer time period.

Given your rule as defined above and using your sample data, let's assume the first day we run the rule is 06 mar 2018 and on this day you've got a transaction of 5000, and then another transaction of 5000 on 11 mar 2018:

1. On which dates would you expect your rule to fire?

2. Have you already considered how to deal with alerts who fire on multiple days based on the same transactions?

You really need to address these questions first before diving into coding.

Re: Sas aml scenario

rajusas — Thu, 22 Mar 2018 09:50:15 GMT

It's just to understand data.
Not works in aml.

Re: Sas aml scenario

rajusas — Thu, 22 Mar 2018 09:56:15 GMT

I am trying to develop code to find sum of currency_amount over a period of
time.

We have 13dec 2017 data only remain data not loaded.
This is Credit card project.
Tr

Re: Sas aml scenario

Patrick — Thu, 22 Mar 2018 10:42:06 GMT

@rajusas

O.K: Using your sample data the following code creates a rolling sum over the last 5 days.

data sample;
  infile datalines;
  input date_key :date9. currency_amount  account_number;
  format date_key date9.;
  datalines;
17mar2018 1000 111
17mar2018 2000 111
16mar2018 1200 111
15mar2018 1000 111
14mar2018 1500 111
13mar2018 1800 111
12mar2018 1600 111
11mar2018 2200 111
10mar2018 2200 111 
09mar2018 2200 111 
08mar2018 2200 111 
07mar2018 2200 111 
;
run;

proc sql;
  create table want as
      select 
        l.date_key,
        l.account_number,
        sum(r.currency_amount) as sum_curr_amt_5days
      from 
        (select distinct account_number, date_key from sample) as l inner join sample as r
      on l.account_number=r.account_number and r.date_key-l.date_key between -4 and 0
      group by l.account_number, l.date_key
  ;
quit;

Result:

Now for your additional condition that the sum must exceed 5000 twice within some given periodicity:

For which of the above dates would you expect the rule to fire?

Re: Sas aml scenario

rajusas — Thu, 22 Mar 2018 12:01:42 GMT

see Iam dealing with aml project.

I have 1 month data starting from 13nov2017 to 13dec2017.Within this data i need to check for every 5 days period of 30 days.

i have to find sum of currency amount for every 5 days.

if sum is greater than 100000 then get output.

aml code iam attaching check once.

parameters

p013_agg_amount             =100000
p013_period_days            =5
p013_num_days               =30
p013_count            =2

p013_total_count = 0;
   p013_total_amount  = 0;
retain p013_T_begin_date;
retain p013_I_begin_date;
 p013_T_begin_date = input(put(rundate_number-&p013_num_days +1,num_to_date.),8.);

do i = n to 1 by -1 while (date_key{i} >=p013_T_begin_date);
  if  p013_T_begin_date <= date_key{i} <= rundate then do;
  
 p013_I_begin_date = input(put((rundate_number)-&p013_period_days+1,num_to_date.),8.);

	do j = n to 1 by -1 while (date_key{j} >=p013_I_begin_date);
		if  p013_I_begin_date <= date_key{j} <= rundate then do;
			if  upcase(account_type_desc{j}) in (&p013_account_type_desc) and 
       upcase(primary_medium_desc{j}) in (&p013_pri_medium_desc) and
       upcase(transaction_cdi_code{j}) in (&p013_cdi_indicator) and 
       upcase(status_desc{j}) in (& p013_status_desc)
    then do;
				%fcf_save_transaction_key (calling_macro=&this_macro_name);
				p013_total_amount = sum(p013_total_amount,currency_amount{j});
        /*p10006_total_count + 1; */
        /*repeat = indexw(p10006_day_list,kstrip(put(date_key{i},8.)));
        if repeat eq 0 and not missing(date_key{i}) then do;
            p10006_day_list = catx(' ',p10006_day_list,put(date_key{i},8.));
            p10006_day_count + 1;*/
        end;
    end;
	end;
	if p013_total_amount  >= &p013_agg_amount then do;
			 p013_total_count= sum( p013_total_count, 1);
	end;
  end;
end;

/* Compare party values to parameters for a match */
if p013_total_count  >=&p013_count then do;
    actual_values_text  =  tranwrd(&p013_threshold_message,'#1',kstrip(put(p013_total_count,nlnum32.)) );
    output &alert_fname;
end;

/* EXIT */
&sysmacroname.:;

Re: Sas aml scenario

Patrick — Fri, 23 Mar 2018 03:04:11 GMT

@rajusas

Now you've got me confused! First you write that what you're doing is unrelated to the SAS AML Solution but now you're telling me that it's actually related to the SAS AML Solution and you post SAS AML scenario code.

The scenario code you've posted appears already to do what you're asking for.

Sooo.... What is this now about? What problem are you trying to solve given that you've got already all the code?

Just making assumptions here: If you're after code which simulates on which run dates the rule would have fired and using the sample data you've provided then below code should do the job.

data sample;
  infile datalines;
  input date_key :date9. currency_amount  account_number;
  format date_key date9.;
  datalines;
21may2018 1000 111
20may2018 1000 111
19may2018 1000 111
15may2018 1000 111
25apr2018 1000 111
21apr2018 1000 111
20apr2018 9000 111
16apr2018 3000 111
15apr2018 1000 111
10apr2018 1000 111
05apr2018 1000 111
30mar2018 1000 111
23mar2018 1000 111
22mar2018 1000 111
19mar2018 1000 111
17mar2018 1000 111
17mar2018 2000 111
16mar2018 1200 111
15mar2018 1000 111
14mar2018 1500 111
13mar2018 1800 111
12mar2018 1600 111
11mar2018 2200 111
10mar2018 2200 111 
09mar2018 2200 111 
08mar2018 2200 111 
07mar2018 2200 111 
;
run;


%let p013_agg_amount =12000;
%let p013_period_days =5;
%let p013_num_days =30;
%let p013_count =2;

proc sql;
  create table rolling5days as
      select 
        l.date_key,
        l.account_number,
        sum(r.currency_amount) as sum_curr_amt_5days
      from 
        (select distinct account_number, date_key from sample) as l inner join sample as r
      on l.account_number=r.account_number and r.date_key-l.date_key between -(&p013_period_days+1) and 0
      group by l.account_number, l.date_key
      order by l.account_number, l.date_key;
  ;
quit;


data alert(drop=_:);
  set rolling5days end=last;
  by account_number date_key;

  array vals {0:%eval(&p013_num_days-1)} 8. _temporary_;
  _ldk=lag(date_key);

  if first.account_number then call missing(of vals[*], _ldk);
  else
    do _i=0 to dim(vals); 

      call missing(vals[mod(_ldk+1+_i,&p013_num_days)]);
      if mod(_ldk+1+_i,&p013_num_days) = mod(date_key,&p013_num_days) then leave;
    end;

  if sum_curr_amt_5days>=&p013_agg_amount then vals[mod(date_key,&p013_num_days)]=sum_curr_amt_5days;
  if n(of vals[*])>=&p013_count then output;

run;

Re: Sas aml scenario

rajusas — Sat, 24 Mar 2018 09:22:38 GMT

hello patrick,

Iam working in AML ENCVIRONMENT. I need to do in datastep only .

Re: Sas aml scenario

Patrick — Sat, 24 Mar 2018 15:21:08 GMT

@rajusas

"I need to do in datastep only " Why?

You also haven't explained me yet why you've first said that this has nothing to do with the SAS AML Solution and now "suddenly" it has everything to do with the SAS AML Solution.

I can't execute and really test it but the scenario code you've posted looks o.k. to me and should work within the SAS AML Solution.

So what's the problem you want to solve? What are you trying to do?

We're now at the point where you need to provide quite a bit more and explicit information what you have and what you need.

- Which AML version?

- What type of header (post the code)?

- Post a SAS data step which creates sample data which can serve as source for your header and scenario code (so with all the columns and the right values for columns used for selections)

- Given that you've already got working scenario code: What's the problem you're trying to solve?

You were at one point writing about "data not loaded". So in case you're trying to create alerts for "old" transactions within the AML Solution which haven't been monitored before ("backwash"): That's currently not possible! - .,.I can be wrong but I believe I've heard that this is under consideration for future AML releases....Won't be easy though as this has a lot of implications.

The code I've posted will allow you to "simulate" the result (the dates with alerts) you would have got if executed against the transactions not loaded. The current version of the SAS AML Solution doesn't allow you to backfill such alerts into the actual AML database.

....whatever answer you're going to give me: If it contains less characters than my post here then it's not detailed enough. I won't continue guessing what you're after 😉

Re: Sas aml scenario

rajusas — Sun, 25 Mar 2018 06:51:14 GMT

Hi I am facing a problem to develop a SAS programme in AML to find out sum of all transactions of an account holder in a single day and also to find out sum of all transactions of an account holder in 5 days.Please help me in this regard

Re:AML scenario_party_header

rajusas — Sun, 25 Mar 2018 07:24:03 GMT

/*------*/
/* Test */
/*------*/
%let runasofdate = 20171213;

%let header_id = 2;

/* 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.PARTY_ALERTS&runasofdate ;

/* Set other variables related to storing transaction keys in a data set. */
%let alert_fname_trans_keys=work._tempalerts_trans_keys;
%let alert_fname_final_trans_keys=STG_ALER.trankey_&header_id._&runasofdate ;
%let alert_fname_temp=work._tempalerts2;

/* Macros for each scenario/risk factor */
/* SBIC010 */
%macro fcf_run_scenario_11790 (
    p010_curr_deposit_min=, 
    p010_credit_indicator=, 
    p010_ctr_amount_limit=, 
    p010_account_type_desc=, 
    p010_currency_acct=, 
    p010_structure_index=, 
    p010_num_days=, 
    p010_pri_medium_desc=, 
    p010_status_desc=, 
    p010_min_days= 
    );

    %include  '!AMLROOT/scenario/scenario_code_active/SBIC010.sas' ;
%mend;

/* End scenario/risk factor macros */

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

/* Begin Header Code */

data &alert_fname
     (drop=_transaction_key_)
     &alert_fname_trans_keys(keep=primary_entity_number scenario_id _transaction_key_)
     ;
   keep primary_entity_number scenario_id rundate actual_values_text entity_level_code
        _transaction_key_
        ;
   format rundate date9. actual_values_text $512. entity_level_code $3.;
   format primary_entity_number $ 75.;
    array DATE_KEY  (&array_dimension) _temporary_ ;
    array TRANSACTION_CDI_CODE  (&array_dimension) $1 _temporary_ ;
    array SECONDARY_MEDIUM_DESC  (&array_dimension) $20 _temporary_ ;
    array STATUS_DESC  (&array_dimension) $20 _temporary_ ;
    array ACCOUNT_NUMBER  (&array_dimension) $50 _temporary_ ;
    array ACCOUNT_TYPE_DESC  (&array_dimension) $20 _temporary_ ;
    array CURRENCY_BASED_ACCOUNT_IND  (&array_dimension) $1 _temporary_ ;
    array PRIMARY_MEDIUM_DESC  (&array_dimension) $20 _temporary_ ;
    array MECHANISM_DESC  (&array_dimension) $20 _temporary_ ;
    array COUNTRY_CODE_3  (&array_dimension) $3 _temporary_ ;
    array CURRENCY_CODE  (&array_dimension) $3 _temporary_ ;
    array CURRENCY_AMOUNT  (&array_dimension) _temporary_ ;
    array TRANSACTION_KEY  (&array_dimension) _temporary_ ;
    set MST_PREP.PARTY_TRANSACTIONS
        (rename=(
        DATE_KEY = temp1
        TRANSACTION_CDI_CODE = temp2
        SECONDARY_MEDIUM_DESC = temp3
        STATUS_DESC = temp4
        ACCOUNT_NUMBER = temp6
        ACCOUNT_TYPE_DESC = temp7
        CURRENCY_BASED_ACCOUNT_IND = temp8
        PRIMARY_MEDIUM_DESC = temp15
        MECHANISM_DESC = temp16
        COUNTRY_CODE_3 = temp17
        CURRENCY_CODE = temp18
        CURRENCY_AMOUNT = temp19
        TRANSACTION_KEY = temp20
        ))
        ;
        by PARTY_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 = "PTY";
          run_scenario_init = 1;
        end;
        if first.PARTY_NUMBER then n = 0;
        n + 1;
        if n le &array_dimension then do;
            DATE_KEY{n} = temp1;
            TRANSACTION_CDI_CODE{n} = temp2;
            SECONDARY_MEDIUM_DESC{n} = temp3;
            STATUS_DESC{n} = temp4;
            ACCOUNT_NUMBER{n} = temp6;
            ACCOUNT_TYPE_DESC{n} = temp7;
            CURRENCY_BASED_ACCOUNT_IND{n} = temp8;
            PRIMARY_MEDIUM_DESC{n} = temp15;
            MECHANISM_DESC{n} = temp16;
            COUNTRY_CODE_3{n} = temp17;
            CURRENCY_CODE{n} = temp18;
            CURRENCY_AMOUNT{n} = temp19;
            TRANSACTION_KEY{n} = temp20;
        end;
        if not last.PARTY_NUMBER then return;
        primary_entity_number = PARTY_NUMBER;

        /* End Header Code Generation */

        /* Call each scenario/risk factor macro after setting scenario_id variable.*/
        scenario_id = 11790;
        /* SBIC010 */
        %fcf_run_scenario_11790 (
            p010_curr_deposit_min=950000 ,
            p010_credit_indicator=%str('C') , 
            p010_ctr_amount_limit=999999999999 ,
            p010_account_type_desc=%str('P') , 
            p010_currency_acct=%str('N') , 
            p010_structure_index=20 ,
            p010_num_days=30 ,
            p010_pri_medium_desc=%str('FUND') , 
            p010_status_desc=%str('SUCCESS') , 
            p010_min_days=1 
        );

   run_scenario_init = 0;
   drop run_scenario_init;
 run;

%fcf_rcset(&syserr);

/*-------------------------------------------  
 * Determine if transaction keys were saved.   
 *-------------------------------------------*/
%macro fcf_process_transaction_keys;
 %global fcf_process_trans_keys;
 %let fcf_process_trans_keys = 0;
 %if %sysfunc(exist(&alert_fname_trans_keys)) %then %do;
   %let dsid = %sysfunc(open(&alert_fname_trans_keys));
   %if &dsid %then %do;
     %let fcf_process_trans_keys = %sysfunc(attrn(&dsid, nlobs));
     %let rc = %sysfunc(close(&dsid));
   %end;
 %end;

 %if &fcf_process_trans_keys gt 0 %then %do;
  /*-------------------------------------------------------  
   * Scenarios may save duplicate transaction keys.          
   * Remove duplicates since record of only one is needed.   
   *-------------------------------------------------------*/
  proc sort data=&alert_fname_trans_keys
             out=&alert_fname_trans_keys(rename=(_transaction_key_ = transaction_key))
       nodupkeys;
    by primary_entity_number scenario_id _transaction_key_;
   run;

  proc sort data=&alert_fname(keep=primary_entity_number scenario_id)
            out=&alert_fname_temp;
    by primary_entity_number scenario_id;
   run;

  /*-------------------------------------------------------------------------  
   * As criteria for an alert is met, scenarios save related transaction       
   * keys.  Later it may be determined that an alert should not be generated   
   * if additional criteria for the alert is not met, so the keys              
   * previously saved are no longer needed.  Here only transactions keys       
   * associated with generated alerts (all alert criteria met) are kept.       
   *-------------------------------------------------------------------------*/
  data &alert_fname_trans_keys;
   merge &alert_fname_temp(in=alertgen) &alert_fname_trans_keys(in=keys);
    by primary_entity_number scenario_id;
    if alertgen and keys;
   run;
 %end;  /*if fcf_process_trans_keys gt 0*/
%mend fcf_process_transaction_keys;
%fcf_process_transaction_keys;

Re: Sas aml scenario

Patrick — Sun, 25 Mar 2018 08:23:34 GMT

@rajusas

You've got the header code (auto-generated header) and you've got scenario code already close to what you'll likely need.

Do you understand how this code works or are you just copy/pasting existing code?

What have you done so far and where do you get stuck?

You're also changing your requirements. It was....

"I have 1 month data starting from 13nov2017 to 13dec2017.Within this data i need to check for every 5 days period of 30 days
i have to find sum of currency amount for every 5 days
if sum is greater than 100000 then get output ."

...at one point you needed at least 2 days over a limit within 30 days...

....and now it is:

"sum of all transactions of an account holder in a single day and also to find out sum of all transactions of an account holder in 5 "

First action: Clearly specify your requirement!

And to repeat:

....whatever answer you're going to give me: If it contains less characters than my post here then it's not detailed enough. I won't continue guessing what you're after 😉

I can help you with this task but I'm not going to do your work for you!

Re: Sas aml scenario

rajusas — Mon, 26 Mar 2018 05:24:30 GMT

Hi Patrick,

when I executed this code it is producing sum of all transactions of an account holder in a day and also giving sum of all transactions minus first,second (it is substracting from top observation value to the bottom of that day) transaction of account holder .But I need only sum of all transactions in a day(that is greater than some value). please help me in this regard.

for example 13/12/2017 2000 5000-2000 =3000

13/12/2017 0 3000-0 3000

13/12/2017 3000 3000-3000 0

totalvalue 5000

if i write condition total value>3000 it is giving

13/12/2017 2000 5000-2000 =3000

13/12/2017 0 2000-0 3000

we have three days data 13dec2017 to 15dec2017.

period_ days=3



%let this_macro_name = &sysmacroname;
array &sysmacroname.1 {&p013_period_days} _temporary_;
/* Empty the array */
do x = 1 to &p013_period_days;
   &sysmacroname.1{x} = 0;
end;

p013_total_count =0;
p013_total_amount=0;
p013_loop_check=0;
retain p013_begin_period_date;



  
p013_begin_period_date = input(put((rundate_number)-&p013_period_days+1,num_to_date.),8.);

                do i = n to 1 by -1 while ( date_key{i} >= p013_begin_period_date);
                                if p013_begin_period_date<= date_key{i}<=rundate  and
                        upcase(account_type_desc{i}) in (&p013_account_type_desc) and 
       upcase(primary_medium_desc{i}) in (&p013_pri_medium_desc) and
       upcase(transaction_cdi_code{i}) in (&p013_cdi_indicator) and 
       upcase(status_desc{i}) in (&p013_status_desc)
    then do;
p013_date_key_num = input(put(date_key{i},date_to_num.),8.);
      temp_date_key = date_key{i};
      do  while (p013_date_key_num = 0);
         temp_date_key = temp_date_key + 1;
         p013_date_key_num = input(put(temp_date_key,date_to_num.),8.);
      end;
      j = rundate_number - p013_date_key_num+1;
      &sysmacroname.1{j}=sum(&sysmacroname.1{j},currency_amount{i});
                  if (&sysmacroname.1{j}) >= &p013_agg_amount and j>1 then do;

if p013_loop_check = 1 then do j = 1 to &p013_period_days;
   if (&sysmacroname.1{j}) >= &p013_agg_amount and j>1 then do;
      p013_x = &sysmacroname.1{j};
      p013_y = 0;
      do k = 1 to &p013_period_days;
         if &sysmacroname.1{k} >= p013_x then p013_y = p013_y + 1;
         p013_z = k;
         if p013_y >= &p013_count then do;
                actual_values_text  =  tranwrd(&r10005_threshold_message,'#1',kstrip(put(&sysmacroname.1{j},nlnum32.)) );
    actual_values_text  =  tranwrd(actual_values_text,'#$', "&currency_code" );
    output &alert_fname;

end;
end;
end;
end;
end;

Re: Sas aml scenario

Patrick — Mon, 26 Mar 2018 09:07:29 GMT

@rajusas

I've asked you quite a few questions/had requests which you ignored so far. My MUST requests before I'm even looking at your code:

1. Clear and full specification for the scenario you want to implement

2. How do you plan to execute this scenario and in what frequency

3. AML version this scenario will get executed under