topic Re: Amount Movement in SAS Programming

Amount Movement

AshokD — Mon, 27 Feb 2023 17:28:29 GMT

Hi,

I'm comparing current month and previous month data, to check for the amount drop and movement between the products.

Please consider the below example:-

Previous month data

CUSTOMER_ID MONTH PRDT_TYPE ACCOUNT_BALANCE
XXX 30-Sep-22 PRDT A 25,000
XXX 30-Sep-22 PRDT B 30,000

Current Month data

CUSTOMER_ID MONTH PRDT_TYPE ACCOUNT_BALANCE
XXX 31-Oct-22 PRDT A 30,000
XXX 31-Oct-22 PRDT B 10,000
XXX 31-Oct-22 PRDT C 15,000

1. In previous month, customer has two products and hold total amount 55k

2. In current month, customer has three products (opened new product - PRDT C) and hold total amount 55k.

3. When comparing previous and current month data - customer moved his/her amount as follows from one to another products.

TO PRODUCT
FROM PRODUCT PRDT A PRDT C
PRDT B 5K 15K

I'm not sure, what would be the best approach to track these amount movements in SAS.

Could someone of you help me on this ?

Thanks in advance for your help.

Re: Amount Movement

PaigeMiller — Mon, 27 Feb 2023 17:55:03 GMT

First, we need example data as working SAS data step code — and not in any other form, not in file attachments, not in screen captures — which you can type in yourself, or follow these instructions.

Re: Amount Movement

ballardw — Mon, 27 Feb 2023 21:06:00 GMT

What are the rules involved if the previous month total "holdings" is less than that of the current month? or more than the current month?

Or the customer did not exist in the previous month?

After a clear definition of what is done there, then perhaps the equal case that you show is a derivative of one of the more complex cases.

Without knowing anything about your products or operations my first thought was that you may be making a bunch of assumptions that aren't quite valid. What if the customer had moved ALL of B into A, then moved some back into B by the next report period? Your proposed summary is not quite what happened.