topic Re: Table with the percentage of the total. in SAS Programming

Table with the percentage of the total.

Pawel_ — Wed, 13 Jan 2021 21:36:10 GMT

Hello,

I want to create table, that showing the percentage of each region in the total sales.

My data:

I wrote something like this:

proc sql;
create table want8 as
Select Genre, NA_Sales/sum(EU_Sales,NA_Sales, JP_Sales, Other_Sales) as NA_Sales format=percent8.2, EU_Sales/sum(EU_Sales,NA_Sales, JP_Sales, Other_Sales)
as EU_Sales format=percent8.2, JP_Sales/sum(EU_Sales,NA_Sales, JP_Sales, Other_Sales) as JP_Sales format=percent8.2
from games order by Genre;
quit;
run;
proc print data=want8;
run;

But I get this result:

I wonder why it doesn't group correctly.

Re: Table with the percentage of the total.

Angel_Larrion — Wed, 13 Jan 2021 22:17:14 GMT

With the code you showed us you are calculating the percentage of each region per game (i.e. each row in the output table is a game ).

What do you want to obtain? (i.e. What does a row represent in the desired output table?)

Re: Table with the percentage of the total.

Pawel_ — Wed, 13 Jan 2021 22:19:05 GMT

I want to calculate percentage of each region per genre.

Re: Table with the percentage of the total.

Angel_Larrion — Wed, 13 Jan 2021 22:26:50 GMT

then you have to group by genre.

proc sql;
create table want8 as
Select Genre, sum(NA_Sales)/sum(EU_Sales,NA_Sales, JP_Sales, Other_Sales) as NA_Sales format=percent8.2, sum(EU_Sales)/sum(EU_Sales,NA_Sales, JP_Sales, Other_Sales)
as EU_Sales format=percent8.2, sum(JP_Sales)/sum(EU_Sales,NA_Sales, JP_Sales, Other_Sales) as JP_Sales format=percent8.2
from games group by Genre order by Genre;
quit;
run;
proc print data=want8;
run;

this code should work

Re: Table with the percentage of the total.

Pawel_ — Wed, 13 Jan 2021 22:31:09 GMT

Unfortunately the result is the same.

Re: Table with the percentage of the total.

Angel_Larrion — Wed, 13 Jan 2021 22:38:46 GMT

you also need to take the sum of the desired region per genre instead of just the sum of the game, that's why I put sum(region_sale) instead of region_sale.

After making this change the result is the same?

Re: Table with the percentage of the total.

Pawel_ — Wed, 13 Jan 2021 22:42:13 GMT

Sorry, I didn't notice this change. However, now the result is this :

Re: Table with the percentage of the total.

Angel_Larrion — Wed, 13 Jan 2021 22:53:14 GMT

instead of

sum(EU_Sales,NA_Sales, JP_Sales, Other_Sales)

you can put

sum(EU_Sales+NA_Sales+ JP_Sales+ Other_Sales)

proc sql;
create table want8 as
Select Genre, sum(NA_Sales)/sum(EU_Sales+NA_Sales+ JP_Sales+ Other_Sales) as NA_Sales format=percent8.2, sum(EU_Sales)/sum(EU_Sales+NA_Sales+ JP_Sales+ Other_Sales)
as EU_Sales format=percent8.2, sum(JP_Sales)/sum(EU_Sales+NA_Sales+ JP_Sales+ Other_Sales) as JP_Sales format=percent8.2
from games group by Genre order by Genre;
quit;
run;
proc print data=want8;
run;

that should work.