Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

How to get value from previous date?

Reply
Occasional Contributor
Posts: 8

How to get value from previous date?

Hello,

 

Is there any way in SAS VA to get value from the previous date (one day before), for example from this table:

--------------------------------------------------------
|    Date   | Company Name | Trx Value | Trx Value D-1 |
--------------------------------------------------------
| 01Jun2016 | ABC          |    10,000 |               |
| 01Jun2016 | DEF          |    11,000 |               |
| 01Jun2016 | XYZ          |    12,000 |               |
| 02Jun2016 | ABC          |    33,000 |        10,000 |
| 02Jun2016 | DEF          |    44,000 |        11,000 |
| 02Jun2016 | XYZ          |    55,000 |        12,000 |
| 03Jun2016 | ABC          |    60,000 |        33,000 |
| 03Jun2016 | DEF          |    70,000 |        44,000 |
| 03Jun2016 | XYZ          |    80,000 |        55,000 |
--------------------------------------------------------

The Trx Value D-1 is New Calculated Item that I would like to get.

 

Regards,

Karina

Super User
Posts: 5,260

Re: How to get value from previous date?

Assuming that you mean by VA already loaded data to LASR.

I haven't worked in depth within VA, but since VA is a kind of standardized reporting tool, it works best with normalized/de-normalized data structures.

My guess is you need to fix calculations like this in the data preparation.

Data never sleeps
Occasional Contributor
Posts: 8

Re: How to get value from previous date?

Hi Linus,

Thanks for your response.

Ok, the calculation is done in Data Preparation/Data Builder by adding new column, how does the SQL expression or the logic should look like (I attach the form)? I'm bit frustrated here :/ 

Kindly enlighten.

Thanks.


DataPrep.png
Super User
Posts: 6,964

Re: How to get value from previous date?

Like @LinusH said, put that into the date before loading into VA.

Sort by company and date and use the lag() function.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,260

Re: How to get value from previous date?

SQL is not aware of row ordering, so any lag() or RETAIN operations isn't available here.
If you are limited to Data Builder you should be able to pull this off by doing a self join with a date = date -1 criteria.
Data never sleeps
Occasional Contributor
Posts: 8

Re: How to get value from previous date?

Is it possible to use Periodic Operators in SAS VA?

I haven't tried it yet.

Ask a Question
Discussion stats
  • 5 replies
  • 326 views
  • 0 likes
  • 3 in conversation