DATA Step, Macro, Functions and more

First 3months transaction amount

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

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;

 

thank you in adavance.


Accepted Solutions
Solution
‎05-16-2017 12:58 AM
PROC Star
Posts: 283

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

View solution in original post


All Replies
PROC Star
Posts: 102

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
PROC Star
Posts: 283

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

Posted in reply to novinosrin

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

 

PROC Star
Posts: 283

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: 7,857

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
Contributor
Posts: 70

Re: First 3months transaction amount

Posted in reply to KurtBremser


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: 7,857

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 169 views
  • 1 like
  • 4 in conversation