sasaggregateaggregate-functionssas-visual-analytics

Calculate % of row total in SAS visual Analytics


I am trying to create a table which has two categories - X an Y. I am trying to create a table in SAS visual analytics that tells me the share of total in each category. My table looks something like this

Category A Catgeoy B Total
40% 60% 100%

I was trying to follow the below link but unfortunately my version of SAS VA does not have Aggregated measure ( tabular) option in it so I do not know how can I proceed forward with it.

How can i go about creating one without the aggregated tabular option

https://communities.sas.com/t5/SAS-Communities-Library/SAS-Visual-Analytics-Report-Example-Percent-of-Total-For-All-For/ta-p/636030


Solution

  • To do this in VA 7.5, we'll use a Crosstab object, a transposed form of your data, and use the "Percent of row total" calculation option within the crosstab. Let's use the below data for our example:

    data have;
       input id x y;
       datalines;
    1 40 60
    2 30 70
    3 90 10
    ;
    run;
    

    Step 1: Transpose to long and create by-groups

    Transpose your data so that it is in a long format, then load it and register it to LASR.

    proc transpose data = have
                   out  = want(rename=(COL1 = value))
                   name = category
                   ;
        by id;
        var x y;
    run;
    

    Output:

    id  category    value
    1   x          40
    1   y          60
    2   x          30
    2   y          70
    3   x          90
    3   y          10
    

    Step 2: Create a crosstab

    Change id to a category, then create a crosstab that looks like this:

    enter image description here

    Go to Options --> Scroll to the bottom --> expand "Totals and Subtotals," and Enable "Totals" for rows and set the Placement to "After."

    enter image description here

    Step 3: Create a row-level Percent Calculation

    Right-click the header value within the table and select "Create and add calculation...".

    enter image description here

    Select "Percent of row total - Sum" under the "Type" drop-down menu.

    enter image description here

    Remove Value as a role from the crosstab graph, format Percent to have 0 decimal places, and you'll have a table with row-wise percentages.

    enter image description here