BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rawindar
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

 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

7 REPLIES 7
s_lassen
Meteorite | Level 14

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

novinosrin
Tourmaline | Level 20

 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
Calcite | Level 5

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

 

novinosrin
Tourmaline | Level 20

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

 

Kurt_Bremser
Super User

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

rawindar
Calcite | Level 5


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

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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