Hi Community,
I have a data that shows the total collections for a specific customer, who experienced a reopening of their order after it was closed. I need from that data to obtain the collections for the reporting month. Here is what I have:
ID | Date | Status | Collection date | Amount owed | ever collected |
1234 | 29-Feb-16 | Open | . | 82.44 | 0 |
1234 | 31-Mar-16 | Open | . | 82.44 | 0 |
1234 | 30-Apr-16 | Open | . | 82.44 | 0 |
1234 | 31-May-16 | Open | . | 82.44 | 0 |
1234 | 30-Jun-16 | Open | . | 82.44 | 0 |
1234 | 31-Jul-16 | Closed | 20-Jul-16 | 82.44 | 82.44 |
1234 | 31-May-18 | reopened | 20-Jul-16 | 82.44 | 82.44 |
And here is what I need (the last column):
ID | Date | Status | Collection date | Amount owed | ever collected | this month collection |
1234 | 29-Feb-16 | Open | . | 82.44 | 0 | 0 |
1234 | 31-Mar-16 | Open | . | 82.44 | 0 | 0 |
1234 | 30-Apr-16 | Open | . | 82.44 | 0 | 0 |
1234 | 31-May-16 | Open | . | 82.44 | 0 | 0 |
1234 | 30-Jun-16 | Open | . | 82.44 | 0 | 0 |
1234 | 31-Jul-16 | Closed | 20-Jul-16 | 82.44 | 82.44 | 82.44 |
1234 | 31-May-18 | reopened | 20-Jul-16 | 82.44 | 82.44 | 0 |
many thanks,
Altijani
I suspect this example will not generalize to your actual data. Can you provide a more robust example?
If it does generalize, then you can use something like:
if put(collection_date, yymm6.) = put(date, yymm6.) then this_month = ever_collected;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.