BookmarkSubscribeRSS Feed
rajusas
Calcite | Level 5
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
24 REPLIES 24
Reeza
Super User

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

rajusas
Calcite | Level 5
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;
Patrick
Opal | Level 21

@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)?

rajusas
Calcite | Level 5
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 
MG18
Lapis Lazuli | Level 10

Hi Raju

I am new to SAS AML so I  wanted to understand how the value of n is getting picked /calculate or how/where is value stored ? I was referring the SAS10006 scenario.  where this array (datekey {i}) definition  is defined ?? Please clarify on this. Many thanks in adavance.

pradeepindia
Calcite | Level 5

Hi Easy way of exploring AML scenario will be to use scenario code generated along with party header. Usually this code will be placed at Lev1/application/sascompliancesolutions/scenario/codegen. This folder should have .sas file of header file name.  Basically n field is calculated using standard incremental variable  n +1 for each group variable. For example if your scenario is using party header, then for each value of unique party_number n is calculated using below logic:

 

if first.PARTY_NUMBER then n = 0;
n + 1;

n value is basically used to in array while transposing  data from row to column for each party number(key variable). just go to code in codegen folder you will be able to understand better. I am using 7.1 version not sure if you are also using same one as SAS is upgrading SAS AML solution periodly hence version 5.1,6.1,7.1 look and feel of application are totally different although underlying scenario logic remains same.

 

 

 

MG18
Lapis Lazuli | Level 10

Many thanks for quick reply. We are using SAS AML 5.1

 

I am able to this location (Lev1/application/sascompliancesolutions/scenario/codegen). as I am new this can u please explain me one Scenario SAS10006 it will great help to me for understanding.

 

when those are getting created ? 

what exactly we are running in order to execute scenario ? is there any job or sas code ?

below is the part of partheader.sas file :-

%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 ;

/*---------------------------------------------------------------
* If transaction keys are saved in the scenario(s) below
* using %save_transaction_key, setting this variable
* will tell %save_transaction_key to save keys in a data set
* instead of the old way using the trigger_trans_keys variable.
* See other related save_trans_keys_in_data_set comments below.
*---------------------------------------------------------------*/
%global save_trans_keys_in_data_set;
%let save_trans_keys_in_data_set = Y;
/* 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;

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

/* Macros for each scenario/risk factor */
/* SAS10005 */
%macro SAS10005 (
p10005_account_type_desc=,
p10005_cdi_indicator=,
p10005_ctr_amount=,
p10005_currency_acct=,
p10005_pri_medium_desc=,
p10005_status_desc=
);

%include '!AMLROOT/scenario/scenario_code_active/SAS10005.000006' ;
%mend;

/* SAS10006 */
%macro SAS10006 (
p10006_account_type_desc=,
p10006_cdi_indicator=,
p10006_currency_acct=,
p10006_num_days=,
p10006_pri_medium_desc=,
p10006_status_desc=,
p10006_trans_limit=,
p10006_trans_today=,
p10006_trans_total=
);

%include '!AMLROOT/scenario/scenario_code_active/SAS10006.000002' ;
%mend;

 

 

 

I have BASE SAS knowledge and SAS DI/BI but new SAS AML please please help in this to understand .

Patrick
Opal | Level 21

@MG18

If asking a new question please don't piggy-back on an existing track but start a new question as else things get really messy.

rajusas
Calcite | Level 5
Hi Patrick
We have 15 lakh observations for one day.
Please reply on above question
Patrick
Opal | Level 21

@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.
 
rajusas
Calcite | Level 5
It's just to understand data.
Not works in aml.
rajusas
Calcite | Level 5
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
Patrick
Opal | Level 21

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

Capture.JPG 

 

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?

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
  • 3379 views
  • 2 likes
  • 5 in conversation