BookmarkSubscribeRSS Feed
rajusas
Calcite | Level 5

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.:;

 

 

Patrick
Opal | Level 21

@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;
rajusas
Calcite | Level 5

hello patrick,

 

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

Patrick
Opal | Level 21

@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 😉

 

rajusas
Calcite | Level 5
/*------*/
/* 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;

rajusas
Calcite | Level 5

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

Patrick
Opal | Level 21

@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!

 

rajusas
Calcite | Level 5

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;

 

Patrick
Opal | Level 21

@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

pradeepindia
Calcite | Level 5

Hi Raju, Not sure still looking for this code. I was looking for similar logic for one of scenario . Have created a code which should suit your requirement. Have created dummy table and performed same transpose happening in header file. Only scenario code part I made a change. I have data for two key variable. My  idea is to create temp array having date_key between start and end date  then use 2 loops to get matching value between this two array and store it is new array. Let me know if it works in scenario. 

 

data temp;
key=1;a=1001;day=1;b=1005;amt=1000;flag='c';output;
key=1;a=1001;day=1;b=1005;amt=5000;flag='c';output;
key=1;a=1001;day=2;b=1005;amt=6000;flag='c';output;
key=1;a=1001;day=3;b=1005;amt=6000;flag='c';output;
key=1;a=1001;day=4;b=1005;amt=2000;flag='c';output;
key=1;a=1001;day=4;b=1004;amt=2000;flag='c';output;
key=2;a=1001;day=1;b=1004;amt=4000;flag='c';output;
key=2;a=1001;day=4;b=1004;amt=4000;flag='c';output;
key=2;a=1001;day=4;b=1004;amt=4000;flag='c';output;
run;

proc sort data=temp;
by key;
run;


data new;
array a (6) _temporary_;
array b (6) _temporary_;
array amt (6) _temporary_;
array flag (6) $1 _temporary_;
array day (6) _temporary_;


set temp (rename= ( a= temp1 b=temp2 amt=temp3 flag=temp4 day=temp5));
by key;


if _n_ eq 1 then do;

end;
if first.key then n = 0;
n + 1;

if n le 6 then do;
a{n} = temp1;
b{n} = temp2;
amt{n} = temp3;
flag{n} = temp4;
day{n} = temp5;

end;
if not last.key then return;


**************************;
****START*****************;

array days_count (4) _temporary_ ;

total_count=0;

do l = 1 to dim(days_count);
days_count[l]=l;
end;

*array to store total amount for each day;
array temp_amount(4) ;

* Calculate total value for each day ;
do j= 1 to 4;
do i = 1 to n;
if day{i}= days_count{j} THEN
temp_amount{j}=sum(temp_amount{j},amt{i});

end;end;


* Identify days for which total amount is greater than limit and storing count value;

do j= 1 to 4;
if temp_amount[j] >5000 then do;
total_count +1;
total_amt_final=sum(total_amt_final,temp_amount[j]);
end;
end;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 24 replies
  • 3380 views
  • 2 likes
  • 5 in conversation