How to get value from previous date?

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,911

Re: How to get value from previous date?

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,

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.

Super User
Posts: 10,527

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
How to convert datasets to data steps
How to post code
Super User
Posts: 5,911

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.

Discussion stats
• 5 replies
• 447 views
• 0 likes
• 3 in conversation