I have a dataset which contain 3 yeas records, the structure below
Company DateTime Amount
In VA, i duplicate the datetime column and make it a Month Year only column.
I want to build a aging report which show 1 years records from todate to the date 1 year before. Example August/2019 - August/2018. The record before August/2018 will sum up and show in one column. The Month Year column will change when the month is change. Example if it is September, the report will from September/2019 - Sept/2018.
Here is the table I wish to show to user.
is it possible to do in SAS VA? Please guide me. I'm using SAS VA(8.3.1) on SAS Viya(V03.04)
Thanks.
Hi!
I think it is possible using the rank function.
First create a numeric variable containing the month (201901, 201902....)
Then use the rank function and list the top 13 months by the new numeric variable.
The rest of the months will automatically sum in the other category if you have the check box selected.
//Fredrik
Hi Fredrike,
Thanks for the answer. I will give a try when my SAS server is ready back.
Hi Fredrike,
I try to create a numeric variable containing month year. When I use rank function, it need me select Top Count or Top percent and then By. In the "By" drop down list, I select amount but the result is not in order for the month in current year. It show the top amount in order. How I can fix this?
Hi!
Choose your variable containg the period from the drop down then doing the rank.
Then select the highest 13 values.
Select the new numeric variable as "by".
I tried to modify my swedish version to english 🙂
Working?
//Fredrik
Still not able to do it. Here is how I assign the rank function. MonthyearInNumber is new create variable which like 201910, 201909
My newly create month year data type is numeric. I try to modify the Rank function, it also not work as expected.
Hi!
Try changing the aggregation from "SUM" to "MAX".
Otherwise you will rank on aggregated date values.
//Fredrik
It's work in correct order. How to sum up all the other month in one column? Currently only show Max 12 month.
I thought it would work by selecting then check box "All other" ?
//Fredrik
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.