topic Re: Processing multiple rows and columns per observation in New SAS User

Processing multiple rows and columns per observation

PS185 — Mon, 29 Apr 2019 17:44:20 GMT

Hello,

I have 2 input data sets shown below

Table 1
Date	Acct	Rank	TxnTypeA	TxnTypeB	TxnTypeC
Jan 1 2018	123	1	70	10	50
Jan 1 2018	123	2	100	50	0
Jan 1 2018	123	3	30	10	40

Table 2
Date	Acct	Amt
Feb 1 2018	123	50
March 1 2018	123	50
April 1 2018	123	300

The Amt value from table 2 should be used to reduce each of the TxnTypeA/TxnTypeB/TxnTypeC amounts in the order of the Rank specified.

For example the 50 from Feb in table 2 should first reduce the 70 from table 1. In this case the result is table 3 as follows. I have also shown how the subsequent amounts of 50 and 300 from table 2 will update the values in table 1.

Output Table 3
Date	Acct	Rank	TxnTypeA	TxnTypeB	TxnTypeC
Feb 1 2018	123	1	70-50=20	10	50
Feb 1 2018	123	2	100	50	0
Feb 1 2018	123	3	30	10	40
March 1 2018	123	1	20-20=0	10	50
March 1 2018	123	2	100-30=70	50	0
March 1 2018	123	3	30	10	40
March 1 2018	123	1	0	10-10=0	50-50=0
March 1 2018	123	2	70-70=0	50-50=0	0
March 1 2018	123	3	30-30=0	10-10=0	40-40=0

The example above is for illustration, I have more columns and many accounts. I was thinking of using arrays but I am not sure how to process the above. Would be glad for any suggestions that the community has, thanks!

Re: Processing multiple rows and columns per observation

Tom — Mon, 29 Apr 2019 21:46:03 GMT

You need explain the rules much better.

When/Why do you move from RANK=1 to RANK=2 etc.? When/Why do you move from first column to second column?

Also what do the dates mean? Are they important?

Re: Processing multiple rows and columns per observation

PS185 — Mon, 29 Apr 2019 21:55:07 GMT

I was attempting to explain the rules using an example but clearly that didn't do it

The ranks are pre-defined. Table 1 shows transactions for a given month (Jan 2019). Table 2 has payments received in subsequent months (Feb-April 2019), these payments should be applied each month to the balances in Table 1 but in the order of the rank specified.

So, in this example, payment amounts received should offset the balances in column TxnTypeA first, and within that column, in the order of increasing rank. Once all of TxnTypeA has been paid off, then the same logic should be followed on the next column TxnTypeB, again in the order of the rank specified.

Thanks for your help in advance!