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

How to create a cumulative plot for any type of X axis in VA 7.2

Reply
Trusted Advisor
Posts: 1,514

How to create a cumulative plot for any type of X axis in VA 7.2

 

There is no way to for VA to natively plot cumulated values yet, unless the X axis is a date axis.

 

Here an example showing how such a plot can de created with a little prior data preparation.

 

This simple plot shows the number of entries in each score band, and the cumulated plot. A couple of filters allow subsetting while the plots always display the correct figures for any filter values.

aaa2.PNG

 

To obtain this graph, you just need to use a dual-axis line chart, and assign the data set variables to the correct roles. The code to prepare the data follows, and only uses three short steps.

 

If needed, a more powerful chart can be build from the same data, where frequency or percentages can be toggled by the user.

Here are the steps to do this.

 

1-Add the dummy list table as a data source and create a custom category called Toggle from the dummy list table.

aaa3.PNG

 

2- Assign this category to a List object (as radio buttons) and to a Drop-Down-List. Filter the radio button list to only keep the 2 values you need. Create an interaction so the radio button drives the Drop-Down List.

aaa4.PNG

 

3- The drop down list is only used here because it is one of the few objects at the moment which can create parameters. Create a new character parameter for the dummy list table and assign to the Drop-Down list object. I called it Toggle-Parameter.

aaa5.PNG

 

4- Create 2 aggregated measures that calculate the percentages or the sums and assign them to the line chart

 

PLOT_BAND=IF ( 'Toggle-Parameter'p = '#' )
          RETURN Sum [_ByGroup_] ('FREQ_BAND'n)
          ELSE   Sum [_ByGroup_] ('FREQ_BAND'n) / Sum [_ByGroup_] ('FREQ_TOTAL'n) * 100

PLOT_SUM=IF ( 'Toggle-Parameter'p = '#' )
         RETURN Sum [_ByGroup_] ('FREQ_SUM'n)
         ELSE   Sum [_ByGroup_] ('FREQ_SUM'n) / Sum [_ByGroup_] ('FREQ_TOTAL'n) * 100

 

5- That’s it. Hide the Drop-down list behind the chart and place the radio buttons. Enjoy your new graph!

 aaa1.PNG

  

Here is the code used to generate the cumulative data. I will not comment each step of the code. It is not very long and the steps are few and simple, so just run it and see what each step does.

 

%***********************************************************************************;
%****************** Create dummy report data ***************************************;
%***********************************************************************************;
data DASHBOARD_DATA;
  format DATE date9.;
  do APP_TYPE='a','b','c';
    do REGION='akl','wai','wgt';
      APPL_NB=100+ranuni(0)*100;
      do I=1 to APPL_NB;  
        SCORE_BAND=byte(int(rannor(0)*6+78));   
        if 'A' <= SCORE_BAND <='Z' then output;
      end;
    end;
  end;
run;

%***********************************************************************************;
%****************** Count entries by nway crossing and score band ******************;
%***********************************************************************************;
proc summary data=DASHBOARD_DATA;
  class APP_TYPE REGION SCORE_BAND;
  types APP_TYPE*REGION 
        APP_TYPE*REGION*SCORE_BAND;
  output out=BAND_SUM(drop=_TYPE_);
run;  

%***********************************************************************************;
%* Do a cartesian product to ensure all crossings have a value for each score band *;
%***********************************************************************************;
proc sql;
  create table BAND_SUM_ALL as 
  select  APP_TYPE, REGION
        , a.SCORE_BAND 
        , _FREQ_ * ( a.SCORE_BAND = b.SCORE_BAND ) as FREQ_BAND   
  from 
    (select unique SCORE_BAND from BAND_SUM )  a
  , BAND_SUM                                   b
  where a.SCORE_BAND ne ' ' 
    and b.SCORE_BAND ne ' '  
  order by APP_TYPE, REGION, a.SCORE_BAND, FREQ_BAND desc  ;
quit;

%***********************************************************************************;
%********* Compute the cumulated value for each crossing and score band ************;
%*********  this will be stored together with total value across all score bands ***;
%***********************************************************************************;
data XTIAN.BAND_SUM_FINAL;
  merge BAND_SUM_ALL
        BAND_SUM(keep  = APP_TYPE  REGION  SCORE_BAND  _FREQ_
                 rename= (_FREQ_=FREQ_TOTAL SCORE_BAND=BAND_TOTAL)
                 where = (BAND_TOTAL eq ' '));                       
  by APP_TYPE REGION ;
  if first.REGION then FREQ_SUM=0;
  FREQ_SUM+FREQ_BAND;
  if SCORE_BAND ne lag(SCORE_BAND);
  drop BAND_TOTAL;
run; 

%***********************************************************************************;
%************* Dummy table used to make lists in VA ********************************;
%***********************************************************************************;
data XTIAN.XTIAN_CUSTOM_LIST;
  do N=1 to 999; 
    output; 
  end;
run; 

 

 

 

 

Ask a Question
Discussion stats
  • 0 replies
  • 576 views
  • 4 likes
  • 1 in conversation