How to derive variables across rows based on a Key column

AbhiD — Mon, 09 May 2016 04:40:01 GMT

Hi All,

I have a LASR table which contains columns as below

ID	TranDate	Flg_1	Val_1
1	31-Dec-15	AA	100
1	31-Jan-15	BB	200
2	31-Dec-15	DD	111
2	31-Jan-15	AA	2345

So i want to create calculated variables based on the comparision of values of Flg_1 for the 2 TranDate selected by the users for a particular ID (Something similar i can do with a merge using By variable as ID)

Currently i am doing it in SAS and loading the results back to LASR server, but i wish to have this dynamic based on the TranDate's that the user of the report select.

Let me know if there is any way to achive this requirement.

SAS VA Version : 7.1

Rgds,

Abhi

Re: How to derive variables across rows based on a Key column

TedStolarczyk — Thu, 19 May 2016 18:04:48 GMT

Abhi:

Formulas within a row (on a row by row basis) is accomplished within the VA Software ala 'Calculated Items'.

Formulas across rows (on a group by basis) is accomplished within the VA Software ala 'Aggregated Measures'.

An example output of 'Aggregated Measures' sort of based on your sample data is attached.

With that said, the "comparison of values" (as your worded it) possibilities is going to depend on what Aggregation functions the software provides (e.g. Min, Max, etc.) vs. the actual "comparison" you are hoping to accomplish. Documentation on Aggregations possible within VA can be found here (note ... this is from v7.3 but should still apply to your v7.1):

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n14e1aiiioais5n1ruouchky8rwo.htm

(and above applies to Visual Analytics Explorer as well as Designer/Reporter).

Otherwise, if you need to perform some complicated IF-THEN-ELSE statement as your "comparison of values", you may have to alternatively transpose the data (prior to VA Reporting) so there's multiple columns (to compare) for each record of the data. Then you would utilize 'Calculated Items' (and not 'Aggregated Measures'). For example ...

IF ( 'Variable A'n > 'Variable B'n )
RETURN (
IF ( 'Variable A'n > 'Variable C'n )
RETURN 'Variable A'n
ELSE 'Variable C'n )
ELSE (
IF ( 'Variable B'n > 'Variable C'n )
RETURN 'Variable B'n
ELSE 'Variable C'n )

Hope this helps,

Ted Stolarczyk, SAS Customer Loyalty team

topic How to derive variables across rows based on a Key column in SAS Visual Analytics

How to derive variables across rows based on a Key column

Re: How to derive variables across rows based on a Key column