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.
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
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
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
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
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
@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.
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
@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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.