Dynamic SAS Code to pick records based on a time period

[buddha_d]

Dear community, 

               I have a real time dataset which I mimicked below. But, I have month of data as shown below

data have;
input TRANSACTION_DATE date9. cust_num :$6. ACCT_num :$6. AMOUNT
;
format TRANSACTION_DATE date9.;
cards;
01OCT2023 123456 ABCDEF 200
01OCT2023 123456 ABCDEF -300
02OCT2023 234567 BCDEFG 159
02OCT2023 123456 ABCDEF 159
03OCT2023 345678 CDEFGH 175
04OCT2023 123456 ABCDEF -200
05OCT2023 123456 ABCDEF -159
06OCT2023 456789 DEFGHI 300
07OCT2023 234567 BCDEFG -159
08OCT2023 123456 ABCDEF -159
09OCT2023 345678 CDEFGH 175
10OCT2023 456789 HYIOFH 456
11OCT2023 123456 ABCDEF -1200
12OCT2023 234567 BCDEFG -1159
13OCT2023 345678 CDEFGH -1175
14OCT2023 456789 HYIOFH -456
15OCT2023 123456 ABCDEF 1200
16OCT2023 234567 BCDEFG 1159
17OCT2023 345678 CDEFGH 1175
18OCT2023 987654 MNBVCX 5000
19OCT2023 987654 MNBVCX -5000
20OCT2023 876543 LKJHGF 8000
21OCT2023 765432 POIUYT 3000
22OCT2023 654321 LKJHGF 4000
23OCT2023 876543 LKJHGF -8000
24OCT2023 765432 POIUYT -3000
25OCT2023 654321 LKJHGF -4000
26OCT2023 111111 AAAAAA 1234
27OCT2023 222222 BBBBBB 3456
28OCT2023 333333 CCCCCC 5674
29OCT2023 444444 DDDDDD 8700 
30OCT2023 555555 FFFFFF 9000
31OCT2023 666666 HHHHHH 4000
;
run;

I need to get the records based on these conditions below:

Scenario1. there is a transaction happened on a date like OCT1st of amount 200 (credited) for customer number '123456' & Account number 'ABCDEF' . If there is any other transaction for the same amount of -200 (debited) for the same customer number '123456' & Account number 'ABCDEF'  with in 6 days of the transaction (i.e. 7th of October) then I want to see those transactions. If the 200 transaction got deducted on 8th of October then I don't need to see that in this scenario.  But that can be applied to the next scenario. 

Scenario2. If there is any transactions with the above condition, but only for 14 days (2 weeks) then I need to have those transactions in my output. These transactions shouldn't include the above condition records. 

                I can write to pull all the month transactions code to apply (PROC SQL using GROUP BY statement), but don't know how to work for the above request. Any help with this is appreciated. 

Any inputs are welcome. Thanks in Advance.  

[SASKiwi · Accepted solution]

Not sure how you want to see the results but here's something that hopefully gets close to what you want for scenario 1.

proc sql;
  create table Want as
  select
   A.cust_num
  ,A.acct_num
  ,A.amount as Amount1
  ,B.amount as Amount2
  ,A.TRANSACTION_DATE as TRANSACTION_DATE1
  ,B.TRANSACTION_DATE as TRANSACTION_DATE2
  from have as A

  inner join have as B
  on A.cust_num = B.cust_num
  and A.acct_num = B.acct_num
  and A.amount = - B.Amount
  and A.TRANSACTION_DATE < B.TRANSACTION_DATE
  and (B.TRANSACTION_DATE - A.TRANSACTION_DATE) between 1 and 6

  order by A.cust_num, A.acct_num, A.amount, A.TRANSACTION_DATE  
  ;
quit;

[buddha_d]

Thanks SASKiwi . It works perfect for me .