topic how to calculate total groub by column and percent in SAS Visual Analytics

how to calculate total groub by column and percent

sara93 — Fri, 08 Mar 2024 19:07:39 GMT

hello,

I am attempting to create the same report in SAS. Could you assist me in calculating the total and percentage for each equipment, as illustrated in the image? I've tried in SAS, but the result doesn't appear to be accurate. I aim for the subtotal to be 100%.

Thank you

Re: how to calculate total groub by column and percent

Cynthia_sas — Fri, 08 Mar 2024 19:36:43 GMT

Hi, What procedure are you using? What code have you tried. Your very wide screen shot it too small to read and too blurry when zoomed. Without data or some idea of the structure of your input data, and some idea of the code you're using and the type of output you need, no one is really able to make constructive suggestions. My instinct is to suggest PROC TABULATE, however, without seeing the structure of the data, I'm not sure that it is the correct instinct.
Cynthia

Re: how to calculate total groub by column and percent

sara93 — Fri, 08 Mar 2024 19:52:31 GMT

thank you for your respond,

im using a cross tab with hierarchy column (epuipement + brand) and parc actif is my measure .

I have calculated Pourcentage by following these steps :

and adding subtotal to my crosstab.

is there any way to calculate the total of percent of the subtotal similar to the following crosstab

Re: how to calculate total groub by column and percent

HunterT_SAS — Fri, 08 Mar 2024 21:24:22 GMT

If you are using Visual Analytics 7.5x on SAS 9.4, then I'm afraid you are out of luck here. There is no way I'm aware of to get 100% for the subtotals in that release I'm afraid. This is due to the nature of that automatic calculated subtotals. You would have to do these calculations in your raw data before loading to LASR/Visual Analytics, then they would be have like normal measures rather than calculated aggregations, and would sum the way you are expecting.

If you are using SAS Viya however, there is a workaround. The automatic calculated subtotals does behave the same way here as it does in SAS 9.4, BUT there is an aggregated measure function called AggregateTable that will allow us to manually create the same calculation, and get the sum to work the way you would like.

To achieve this, you would need to create a calculated item that looks like this. I have substituted your column names so hopefully you can just copy and paste this into a new calculated item:

Sum [_ByGroup_] ('Parc actif'n) / AggregateTable(_Sum_, Table(_Sum_, Fixed('Categorie'n), 'Parc actif'n))

The first part, Sum [_ByGroup_] ('Parc actif'n), is just the normal sum for each Categorie/Operatuer combination.
The second part, AggregateTable(_Sum_, Table(_Sum_, Fixed('Categorie'n), 'Parc actif'n)) is the sum of just the Categorie data item.
And of course dividing the two gets us the subtotal.

Then when you turn on subtotals in the crosstab, it should get you that 100%. I've mocked up an example here using the SASHELP.CARS data with Origin, Type, and MSRP so you can see the difference:

Hopefully that helps you out!

Re: how to calculate total groub by column and percent

sara93 — Wed, 13 Mar 2024 11:12:00 GMT

Thank you for your response. Unfortunately, I am working with SAS VA 7.5 on SAS 9.4. Therefore, I will need to perform these calculations on my raw data before loading it into LASR/Visual Analytics.