How to create a financial aging report in SAS VA

[Derick]

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.

[FredrikE]

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

[Derick]

Hi Fredrike,

   Thanks for the answer.  I will give a try when my SAS server is ready back.

[Derick]

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?

[FredrikE]

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

[Derick]

Still not able to do it.  Here is how I assign the rank function.  MonthyearInNumber is new create variable which like 201910, 201909

[Derick]

My newly create month year data type is numeric.  I try to modify the Rank function, it also not work as expected.

[FredrikE]

Hi!

Try changing the aggregation  from "SUM" to "MAX".

Otherwise you will rank on aggregated date values.

//Fredrik

[Derick]

It's work in correct order. How to sum up all the other month in one column? Currently only show Max 12 month.

[FredrikE]

I thought it would work by selecting then check box "All other" ?

//Fredrik