BookmarkSubscribeRSS Feed
Edoedoedo
Pyrite | Level 9

Hi,

I'm getting stuck with this issue: I have a dataset which looks like this:

START_DATE | END_DATE | ID | VALUE

01/01/2016 | 31/12/9999 | 1 | 300

01/01/2016 | 02/01/2016 | 2 | 500

02/01/2016 | 31/12/9999 | 2 | 100

 

It is an historical table. It means that:

- ID #1 had a value of 300 on 01/01/2016, and it has still this value today

- ID #2 had a value of 500 on 01/01/2016, but then on 02/01/2016 it changed its value to 100, and it has still this value today.

So briefly the table says that each row is valid from START_DATE (included) until END_DATE (excluded).

 

I want to create a report in VA, where I show a simple line graph where, for each day, I aggregate VALUE only for the records that are valid in that day. In this example, the line graph should have:

- on X axis: 01/01/2016 and 02/01/2016

- on Y axis: 800 and 400

Why? Because on 01/01/2016 ID #1 has value 300 and ID #2 has value 500 so 800, and on 02/01/2016 ID #1 has value 300 and ID #2 has value 100 so 400.

 

How could I achieve this in VA? I must use directly this table (and of course any calculated variable or parameter) but I cannot rework the table to get it nicer.

 

Thanks a lot for any help.

Regards

2 REPLIES 2
Sam_SAS
SAS Employee

Hello,

 

You will probably want use some DATA step processing in the query for your data set. You can edit the SAS code for your data query in the visual data builder (access "Data Preparation" from the VA home page.)

 

 

Hopefully that will help,

Sam

Edoedoedo
Pyrite | Level 9
Thank you for your answer, but unfortunately I'm not allowed to do that by the admin, all I can do is "Create Report" section and use this table as is...

sas-innovate-2024.png

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.

 

Register now!

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
  • 2 replies
  • 833 views
  • 0 likes
  • 2 in conversation