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

How to build a fully user-driven comparison plot chart in VA 7.2

Reply
Trusted Advisor
Posts: 1,328

How to build a fully user-driven comparison plot chart in VA 7.2

Let's say we need users to choose from a number of measures (sums and percentages here) and display them for any combination of user-chosen categories.

  

In order to do this, just a few steps are necessary. Once again, the data needs to be pre-summarised since VA cannot do this kind of trick yet, but a simple proc summary is all that’s required (see code at the bottom).

 

In this example, we’ll compare income sources from our motorbike business. We have a few filters that also allow us to choose what categories to display.

 

Example1 

 

Example2

 

Here are the steps:

 

1- Create a customs object with 3 line plots. Give the plots a common grouping variable.

 custom object

2- Create a custom category from the dummy table. It contains the list entries for the 3 drop-down lists.

 

custom list

 

3- Assign this custom category to the 3 drop-down lists. Create 3 parameters and assign them to the drop-down lists as well. I called the custom category Plot List and the parameters Plot Measure 1, 2 and 3. Filter the drop-down lists to only keep the populated values. I also added images of plot lines beside each drop-down-list object.

 

List parameters

 

4- Create 6 Aggregated Measures for the main table to calculate the percentages

 

'% Rental #'n = Sum [_ByGroup_] ('Rental #'n) / Sum [_ByGroup_] (FREQ_ALL)

'% Lease #'n = Sum [_ByGroup_] ('Lease #'n) / Sum [_ByGroup_] (FREQ_ALL)

'% Sale #'n   = Sum [_ByGroup_] ('Sale #'n)   / Sum [_ByGroup_] (FREQ_ALL)

 '% Rental $'n = Sum [_ByGroup_] ('Rental $'n) / Sum [_ByGroup_] (AMOUNT_ALL)

'% Lease $'n = Sum [_ByGroup_] ('Lease $'n) / Sum [_ByGroup_] (AMOUNT_ALL)

'% Sale $'n   = Sum [_ByGroup_] ('Sale $'n)   / Sum [_ByGroup_] (AMOUNT_ALL)

 

 

4- Create 3 Aggregated Measures for the main table indicating what to plot depending on the user choices.

 

'Plot Measure 1'n=

IF     ( 'Plot Measure 1'p = 'Rental #' ) RETURN Sum [_ByGroup_] ('Rental #'n)

ELSE IF ( 'Plot Measure 1'p = 'Lease #' )   RETURN Sum [_ByGroup_] ('Lease #'n)

ELSE IF ( 'Plot Measure 1'p = 'Sale #' )   RETURN Sum [_ByGroup_] ('Sale #'n)

ELSE IF ( 'Plot Measure 1'p = 'Rental $' ) RETURN Sum [_ByGroup_] ('Rental $'n)

ELSE IF ( 'Plot Measure 1'p = 'Lease $' )   RETURN Sum [_ByGroup_] ('Lease $'n)

ELSE IF ( 'Plot Measure 1'p = 'Sale $' )   RETURN Sum [_ByGroup_] ('Sale $'n)

ELSE IF ( 'Plot Measure 1'p = '% Rental #') RETURN '% Rental #'n * 100

ELSE IF ( 'Plot Measure 1'p = '% Lease #' ) RETURN '% Lease #'n * 100

ELSE IF ( 'Plot Measure 1'p = '% Sale #' ) RETURN '% Sale #'n * 100

ELSE IF ( 'Plot Measure 1'p = '% Rental $') RETURN '% Rental $'n * 100

ELSE IF ( 'Plot Measure 1'p = '% Lease $') RETURN '% Lease $'n * 100

ELSE IF ( 'Plot Measure 1'p = '% Sale $' ) RETURN '% Sale $'n * 100

ELSE .

 

 

5- Assign the variables to the custom plot chart

plot parameters

 

6- Set the interactions.

interactions

 

7- That’s it. You can now display any category combination and up to three of any measure.

 

example3

 

Here is the code. Remove the comments, it seems that this web site adds weird characters for some reason.

 

%*************************************************​**********************************;
%****************** Create dummy report data ***************************************;
%*************************************************​**********************************;
data REPORT_DATA;
   format DATE date9. AMOUNT dollar12.0;
   do I=1 to 10000;
      REGION      = choosec(1+ranuni(0)*3, 'Manawatu-Whanganui', 'Taranaki', 'Waikato' );
      CATEGORY    = choosec(1+ranuni(0)*3, 'Yellow', 'Fast' , 'Big'  );
      INCOME_TYPE = choosec(1+ranuni(0)*3, 'Rental', 'Lease', 'Sale' );
      DATE        = ranuni(0)*500+20e3;
      AMOUNT      = ranuni(0) * length(cats(of _CHARACTER_))**2 * DATE**.5;
      output;
   end;
run;

%*************************************************​**********************************;
%****************** Count entries for all crossings ********************************;
%*************************************************​**********************************;
proc summary data=REPORT_DATA missing chartype;
  format DATE monyy.;
  class  DATE REGION CATEGORY INCOME_TYPE ;
  var    AMOUNT;
  output out=REPORT_DATA_SUM(where=(_TYPE_=:'1'))
         sum=;
run;
      
%*************************************************​**********************************;
%****************** Reorganise data: one variable per plot, derive totals **********;
%*************************************************​**********************************;
data XTIAN_TEST;
  merge REPORT_DATA_SUM(rename=(_FREQ_=FREQ_SALE   AMOUNT=AMOUNT_SALE)
                        where=(INCOME_TYPE=:'S'))
        REPORT_DATA_SUM(rename=(_FREQ_=FREQ_LEASE  AMOUNT=AMOUNT_LEASE)
                        where=(INCOME_TYPE=:'L'))
        REPORT_DATA_SUM(rename=(_FREQ_=FREQ_RENTAL AMOUNT=AMOUNT_RENTAL)
                        where=(INCOME_TYPE=:'R')) ;
  by _TYPE_ DATE REGION CATEGORY ;
  PLOT       = catx(' - ', REGION , CATEGORY  );
  PLOT       = coalescec(PLOT     ,'All');
  REGION     = coalescec(REGION   ,'All');
  CATEGORY   = coalescec(CATEGORY ,'All');
  FREQ_ALL   = sum(FREQ_SALE  , FREQ_LEASE  , FREQ_RENTAL);
  AMOUNT_ALL = sum(AMOUNT_SALE, AMOUNT_LEASE, AMOUNT_RENTAL);
  drop INCOME_TYPE _TYPE_;
  label FREQ_RENTAL   = 'Rental #'
        FREQ_LEASE    = 'Lease # '
        FREQ_SALE     = 'Sale #  '
        AMOUNT_RENTAL = 'Rental $'
        AMOUNT_LEASE  = 'Lease $ '
        AMOUNT_SALE   = 'Sale $  '
        RECD_DATE     = 'Date    ';
run; 

%*************************************************​**********************************;
%************* Dummy table used to make lists in VA ********************************;
%*************************************************​**********************************;
data XTIAN_CUSTOM_LIST;
  do N=1 to 999; 
    output; 
  end;
run; 
Post a Question
Discussion Stats
  • 0 replies
  • 509 views
  • 2 likes
  • 1 in conversation