topic Re: proc tabulate-Percentage from overall sum in SAS Programming

proc tabulate-Percentage from overall sum

Ronein — Wed, 25 Apr 2018 08:34:12 GMT

Hello friends

I have a question please.

I run the following code.

I want to display values with % symbol.

I want also to add Totals in each column .

I want also to add Totals in each Row .

I want to modify header "product" to "Product Name".

I want to modify header "Actual sales" to "Sales".

Can anyone help to modify the code to get it?

proc tabulate data=sashelp.prdsale;
class country product;
var actual;
table country,product*actual*pctsum/box="Country Name";
run;

Re: proc tabulate-Percentage from overall sum

RW9 — Wed, 25 Apr 2018 09:04:29 GMT

As you have not provided test data in the form of a datastep and what the output should look like this will be vague. You can look at the various papers out there and the documentation:

http://www2.sas.com/proceedings/sugi24/Handson/p153-24.pdf

http://www2.sas.com/proceedings/forum2008/264-2008.pdf

(Note, whilst I put links to those documents I would strongly advise not to code all in uppercase like they show, and to finish blocks off with run; statements.)

To display values with a percentage sign you use the format statement.

Headers you use the title statement.

Totals, its a matter of putting the (sum) statistic in the place you want it.

Re: proc tabulate-Percentage from overall sum

Cynthia_sas — Wed, 25 Apr 2018 14:19:42 GMT

Hi:
I like this paper on TABULATE:
http://support.sas.com/resources/papers/proceedings11/173-2011.pdf and the one listed below that is specifically about percentages.

I want to display values with % symbol.
>> Investigate the use of a PICTURE format as shown on page 12 of this paper: https://support.sas.com/resources/papers/proceedings13/134-2013.pdf

I want also to add Totals in each column .
I want also to add Totals in each Row .
>> Do you want row percents? (every row adds up to 100)
>> Do you want column percents? (every column adds up to 100)
>> Do you want percent of the grand total (lower right cell adds up to 100)
(see pages 16 and 17 in this http://support.sas.com/resources/papers/proceedings11/173-2011.pdf)

>> to get a final Total Row at the bottom of the other rows,
you specify ALL in the TABLE statement for the ROW dimension (see page 5 in this paper http://support.sas.com/resources/papers/proceedings11/173-2011.pdf )

I want to modify header "product" to "Product Name".
I want to modify header "Actual sales" to "Sales".
>> Investigate how to use a label in the TABLE Statement
>> and how to use the KEYLABEL statement

There have been previous postings on the use of PROC TABULATE, you might find some examples if you search.

Hope this points you in a useful direction.

Cynthia

Re: proc tabulate-Percentage from overall sum

Astounding — Wed, 25 Apr 2018 14:28:30 GMT

Looks like you have 5 questions here.

Some of these are easy. Do you have access to the documentation? To label variables:

table country,product='Product Name'*actual='Sales'*pctsum/box="Country Name";

Similarly, to add a % sign use a format (but there may be a drawback):

table country,product*actual*pctsum*f=percent9.2/box="Country Name";

The drawback: the percent format expects numbers between 0 and 1. You may have to add a multiplier (uh-oh, have to read the documentation here).

To get row or column totals, PROC TABULATE uses the keyword ALL (uh-oh again, have to read the documentation). Something like:

table country all,(product*actual all)*pctsum/box="Country Name";

You may need to fiddle with it to get it to come out right. Also note, it's not clear why you would want totals for both rows and columns. Wouldn't some of those totals have to be 100%? You haven't really shown what the final report should look like, but you're likely to go through a few tests, experiments, and iterations until it comes out the way you want it.

********************* EDITED:

Looks like Cynthia and I were composing our responses at the same time. Trust whatever @Cynthia_sas says on this ... she's the expert when it comes to PROC TABULATE.