BookmarkSubscribeRSS Feed
Derick
Obsidian | Level 7

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.  

Untitled.png

 

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.

9 REPLIES 9
FredrikE
Rhodochrosite | Level 12

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
Obsidian | Level 7

Hi Fredrike,

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

 

Derick
Obsidian | Level 7

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
Rhodochrosite | Level 12

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 🙂

 

image.png

Working?

 

//Fredrik

Derick
Obsidian | Level 7

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

 

sas.png

Derick
Obsidian | Level 7

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

 

sa1.png

FredrikE
Rhodochrosite | Level 12

Hi!

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

 

Otherwise you will rank on aggregated date values.

 

//Fredrik

Derick
Obsidian | Level 7

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

FredrikE
Rhodochrosite | Level 12

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

//Fredrik

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 9 replies
  • 1840 views
  • 1 like
  • 2 in conversation