To count transactions happening every month with same amount.

[paulsud1]

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. 

[PeterClemmensen]

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

[paulsud1]

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.

[paulsud1]

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. 

[andreas_lds]

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