topic to count transactions happening every month with same trans amount. in SAS Programming

To count transactions happening every month with same amount.

paulsud1 — Mon, 21 Oct 2019 06:51:43 GMT

Hi everyone, I am new in SAS and need help in below problem.

I have a dataset like this, EXCEPT the Expec_Month Column which is desired.

ID Date amt Month Month1

1 03MAY2019 1500 MAY MAY

1 03JUN2019 1500 JUN JUN

1 05JUL2019 1500 JUL JUL

1 03AUG2019 1500 AUG AUG

2 30MAY2019 700 MAY MAY

2 01JUL2019 700 JUL JUN

2 30JUL2019 700 JUL JUL

2 30AUG2019 700 AUG AUG

2 04MAY2019 1000 MAY MAY

2 08JUN2019 1000 JUN JUN

2 08JUL2019 1000 JUL JUL

2 05AUG2019 1000 AUG AUG

3 02MAY2019 500 MAY MAY

3 02JUN2019 500 JUN JUN

3 01JUL2019 500 JUL JUL

3 31JUL2019 500 JUL AUG

I want to find transactions happening every month with same trans amount.

ID Date amt Month Month1

2 30MAY2019 700 MAY MAY

2 01JUL2019 700 JUL JUN

for cases like this above,

if Date is 30MAY2019 then the Month = MAY

now if the next Date of the same customer with similar amt lies in between +-5 days then the transaction month needs to be updated like here next Date is 01JUL2019 falling in between +-5 days of 30MAY2019, so Month1 = JUN not JULY

AND my desired final output would be like this

ID Amt MAY_Tr Jun_Tr JUL_Tr Aug_Tr

1 1500 1 1 1 1

2 700 1 1 1 1

2 1000 1 1 1 1

3 500 1 1 1 1

Thank you in advance. Let me know if you have any doubts with the question.

to count transactions happening every month with same trans amount.

paulsud1 — Mon, 21 Oct 2019 06:03:41 GMT

Hi everyone, I am new in SAS and need help in below problem.

I have a dataset like this, EXCEPT the Expec_Month Column which is desired.

ID Trans_date Trans_amt Month Expec_Month

1 03MAY2019 1500 MAY MAY

1 03JUN2019 1500 JUN JUN

1 05JUL2019 1500 JUL JUL

1 03AUG2019 1500 AUG AUG

2 30MAY2019 700 MAY MAY

2 01JUL2019 700 JUL JUN

2 30JUL2019 700 JUL JUL

2 30AUG2019 700 AUG AUG

2 04MAY2019 1000 MAY MAY

2 08JUN2019 1000 JUN JUN

2 08JUL2019 1000 JUL JUL

2 05AUG2019 1000 AUG AUG

3 02MAY2019 500 MAY MAY

3 02JUN2019 500 JUN JUN

3 01JUL2019 500 JUL JUL

3 31JUL2019 500 JUL AUG

I want to find transactions happening every month with same trans amount.

ID Trans_date Trans_amt Month Expec_Month

2 30MAY2019 700 MAY MAY

2 01JUL2019 700 JUL JUN

for cases like this above,

if trans_date is 30MAY2019 then the tran_month = MAY

now if the next tran_date of the same customer with simlar tran_amount lies in between +-5 days then the transaction month needs to be updated like here next tran_date is 01JUL2019 falling in between +-5 days of 30MAY2019, so expect_month = JUN not JULY

AND my desired final output would be like this

ID Trans_amt MAY_Tr Jun_Tr JUL_Tr Aug_Tr

1 1500 1 1 1 1

2 700 1 1 1 1

2 1000 1 1 1 1

3 500 1 1 1 1

Thank you in advance. Let me know if you have any doubts with the question.

Re: To count transactions happening every month with same amount.

PeterClemmensen — Mon, 21 Oct 2019 06:54:51 GMT

I don't understand this. 01JUL2019 does not fall within +-5 days of 01JUL2019?

Re: To count transactions happening every month with same amount.

paulsud1 — Mon, 21 Oct 2019 07:04:11 GMT

ID Date amt Month Month1(Desired Month)

2 30MAY2019 700 MAY MAY

2 01JUL2019 700 JUL JUN

2 30JUL2019 700 JUL JUL

2 30AUG2019 700 AUG AUG

hypothesis I want to prove: for every ID there are 4 transactions with similar amount in 4 consecutive months (MAY,JUN,JUL,AUG).

now there are cases above, where for second observation where date is 01JUL2019 the month is JUL right? but I want to make it JUN.

because the first observation is of 30MAY2019, so if date of next observation lies between -5DAYS <30JUN2019< +5DAYS, in this case 01JUL2019 falls in between this condition so the Month1 should be changed to JUN instead of JULY.

Got my question? let me know. thanks.

Re: to count transactions happening every month with same trans amount.

andreas_lds — Mon, 21 Oct 2019 09:43:33 GMT

Please stop asking the same question multiple times. That way you won't get any useful answer sooner. I will start merging your posts.