topic Re: First 3months transaction amount in SAS Programming

First 3months transaction amount

rawindar — Mon, 15 May 2017 11:08:26 GMT

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;

thank you in adavance.

Re: First 3months transaction amount

s_lassen — Mon, 15 May 2017 11:45:04 GMT

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

Re: First 3months transaction amount

novinosrin — Mon, 15 May 2017 12:22:13 GMT

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

Re: First 3months transaction amount

rawindar — Mon, 15 May 2017 12:31:16 GMT

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

Re: First 3months transaction amount

novinosrin — Mon, 15 May 2017 12:34:17 GMT

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

Re: First 3months transaction amount

Kurt_Bremser — Mon, 15 May 2017 12:43:18 GMT

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

Re: First 3months transaction amount

rawindar — Tue, 16 May 2017 04:58:12 GMT

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

Re: First 3months transaction amount

Kurt_Bremser — Tue, 16 May 2017 09:03:51 GMT

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