transaction amount

[BrahmanandaRao]

data have;
input tx_id tx_date account_id amt;
informat tx_date date9.;
format tx_date date9.;
cards;
1 01jan2012 101 1000
2 02jan2012 101 1000
3 03jan2012 101 1000
4 04jan2012 101 1000
5 05jan2012 101 1000
6 06jan2012 101 1000
7 07jan2012 101 1000
8 08jan2012 101 1000
9 09jan2012 101 1000
10 10jan2012 101 1000
11 01jan2012 102 5000
12 01jan2012 103 100
;
run;

proc sql;
select tx_id,amt,sum(amt)as req from have
group by  amt,tx_id
having sum(amt)>=9000;
quit;


I want to find more than one transaction each custormer_id and sum of amount>9000 for 

[PeterClemmensen]

What is the question here?

[BrahmanandaRao]

1.How to get more than one transaction for each custormer id

2.How to get more than total amt >9000 for each custromer id

[PeterClemmensen]

Can you post your desired result given the sample data?

[BrahmanandaRao]

1.How to get more than one bank transaction custromers_id this is my question

how to get using proc sql and data step

[Tom]

---

@BrahmanandaRao wrote:

1.How to get more than one transaction for each custormer id

2.How to get more than total amt >9000 for each custromer id

---

"Get" as in define a new variable?

proc sql;
create table want as 
select *
     , sum(amt) as total_amt
     , count(*) as number_tx
     , sum(amt) > 9000 as more_than_9000
     , count(*) > 1 as more_then_one_tx 
from have
group by account_id
order by account_id, tx_date 
;
quit;

[Patrick]

Below if both condition must be true - else change the AND to a OR in the having clause.

data have;
  input tx_id tx_date account_id amt;
  informat tx_date date9.;
  format tx_date date9.;
  cards;
1 01jan2012 101 1000
2 02jan2012 101 1000
3 03jan2012 101 1000
4 04jan2012 101 1000
5 05jan2012 101 1000
6 06jan2012 101 1000
7 07jan2012 101 1000
8 08jan2012 101 1000
9 09jan2012 101 1000
10 10jan2012 101 1000
11 01jan2012 102 5000
12 01jan2012 103 100
;

proc sql;
  select 
    l.*,
    r.sum_amt,
    r.n_transactions
  from have l
  inner join
  (
  select 
    account_id,
    sum(amt) as sum_amt,
    count(*) as n_transactions
  from have
  group by account_id
  having sum(amt)>=9000 and count(*)>1
  ) r
  on l.account_id=r.account_id
  ;
quit;

[SK_11]

Are you looking for this?

proc sql;
select tx_id, amt, sum(amt)as req, count(tx_id) as tx_count from have
group by account_id
having sum(amt)>=9000 and count(tx_id)>1
order by tx_id
;quit;

Output:

tx_id	amt	req	tx_count
1	1000	10000	10
2	1000	10000	10
3	1000	10000	10
4	1000	10000	10
5	1000	10000	10
6	1000	10000	10
7	1000	10000	10
8	1000	10000	10
9	1000	10000	10
10	1000	10000	10