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

How to derive variables across rows based on a Key column

Reply
Contributor
Posts: 26

How to derive variables across rows based on a Key column

Hi All,

 

I have a LASR table which contains columns as below

 

IDTranDateFlg_1Val_1
131-Dec-15AA100
131-Jan-15BB200
231-Dec-15DD111
231-Jan-15AA

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

SAS Employee
Posts: 32

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

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#n14e1aiiioais5n1ruouc...

(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

Attachment
Post a Question
Discussion Stats
  • 1 reply
  • 217 views
  • 0 likes
  • 2 in conversation