Solved
Contributor
Posts: 70

# First 3months transaction amount

Hi,

I have below dataset with 4 coloumns.As shown below Account opening date is different to every account.

after that he may transact in first 3months from account opening date or may not transact.

data account;

input account_no \$ acc_open_date :date9. trans_date :date9. transamt;

format acc_open_date trans_date date9.;

datalines;

08660123 02NOV2016 15DEC2016 1000

08660123 02NOV2016 16DEC2016 100

08660123 02NOV2016 19DEC2016 100

08660123 02NOV2016 15JAN2017 1000

08660123 02NOV2016 25JAN2017 1000

08660123 02NOV2016 1FEB2017 1000

08660123 02NOV2016 10FEB2017 1000

08660123 02NOV2016 11FEB2017 1000

08660123 02NOV2016 11MAR2017 1000

08660123 02NOV2016 21MAR2017 1000

08660456 02JUN2016 21OCT2016 1000

08660456 02JUN2016 21NOV2016 1000

08660456 02JUN2016 21DEC2016 1000

;

here i would need sum(transamt) for the first 3months(if account open month is NOv then trans amount for NOV DEC JAN from the account opeinng date group by account no.

in this case i need

08660123 3200

08660456  0

I tried below query but it's not giving me correct result.

proc sql;

select account_no,sum(transamt) as totaltransaction

from account

where month(trans_date) between month(acc_open_date) and month(intnx('month',acc_open_date,2))

group by account_no;

quit;

Accepted Solutions
Solution
‎05-16-2017 12:58 AM
Super User
Posts: 2,068

## Re: First 3months transaction amount

[ Edited ]

Asssuming some accounts may have transacted only for 2 months, for eg any account that may have opened only a couple of months ago:

proc sql;

select account_no,sum(transamt) as totaltransaction

from account

where intck('month',acc_open_date,trans_date)+1<=3

group by account_no;

quit;

Regards,

Naveen Srinivasan

All Replies
PROC Star
Posts: 277

## Re: First 3months transaction amount

Rawindar,

The MONTH() function just gives you the number of the month in the year. Try using just the INTNX() function instead:

```proc sql;
select account_no,sum(transamt) as totaltransaction
from account
where trans_date between acc_open_date and
intnx('month',acc_open_date,3,'SAME')-1
group by account_no;
quit;```

By using the SAME alignment you get the same day of month as the base date; I subtracted one from that as the date thus calculated is actually the first date in the fourth month.

Regards,

Søren

Solution
‎05-16-2017 12:58 AM
Super User
Posts: 2,068

## Re: First 3months transaction amount

[ Edited ]

Asssuming some accounts may have transacted only for 2 months, for eg any account that may have opened only a couple of months ago:

proc sql;

select account_no,sum(transamt) as totaltransaction

from account

where intck('month',acc_open_date,trans_date)+1<=3

group by account_no;

quit;

Regards,

Naveen Srinivasan

Contributor
Posts: 70

## Re: First 3months transaction amount

Hi Søren/Navin

thanks for immediate response.

I tried both of your queries ,Butunfortunately it did not extract any rows.

Thannk you.

Regards,

Rawindarreddy

Super User
Posts: 2,068

## Re: First 3months transaction amount

I tested using your sample that you gave. It did fetch the sum for the id 08660123

And this is what i got:

account_no totaltransaction

----------------------------

08660123 3200

Super User
Posts: 10,600

## Re: First 3months transaction amount

rawindar wrote:

Hi Søren/Navin

thanks for immediate response.

I tried both of your queries ,Butunfortunately it did not extract any rows.

Thannk you.

Regards,

Rawindarreddy

For reference:

This code

``````data account;
input account_no \$ acc_open_date :date9. trans_date :date9. transamt;
format acc_open_date trans_date date9.;
datalines;
08660123 02NOV2016 15DEC2016 1000
08660123 02NOV2016 16DEC2016 100
08660123 02NOV2016 19DEC2016 100
08660123 02NOV2016 15JAN2017 1000
08660123 02NOV2016 25JAN2017 1000
08660123 02NOV2016 1FEB2017 1000
08660123 02NOV2016 10FEB2017 1000
08660123 02NOV2016 11FEB2017 1000
08660123 02NOV2016 11MAR2017 1000
08660123 02NOV2016 21MAR2017 1000
08660456 02JUN2016 21OCT2016 1000
08660456 02JUN2016 21NOV2016 1000
08660456 02JUN2016 21DEC2016 1000
;
run;

proc sql;
select account_no,sum(transamt) as totaltransaction
from account
where intck('month',acc_open_date,trans_date)+1<=3
group by account_no;
quit;
``````

produced this result (SAS 9.4/AIX/EG 7.1):

```account_no  totaltransaction
----------------------------
08660123                3200
```

Message to all: please use the "little running man" or {i} icons to post SAS code and log. The main posting window scrambles the formatting and adds surplus lines.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 70

## Re: First 3months transaction amount

Thanks Søren/Naveen/Kurt

Yes it works with both of your queries.Seems to problem with my EG session.It worked in new session.

thank you so much.

Regards,

Rawindarreddy

Super User
Posts: 10,600

## Re: First 3months transaction amount

rawindar wrote:

Thanks Søren/Naveen/Kurt

Yes it works with both of your queries.Seems to problem with my EG session.It worked in new session.

thank you so much.

Regards,

Rawindarreddy

See Maxim 9.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.