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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.