COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### can proc tabulate do the ratio of sums?

• Email
• Follow

```I have heard that I can make proc tabulate calculate the ratio of two
columns, but I can't figure out how to do it. For example if I want
the share of tax in income by year and ask for:

proc tabulate;
class year;
var income tax;
table year,income*sum  tax*sum  tax*pctsum<year*income>

the first two columns are income and tax by year, as expected, but the
third column is the column share - the share of that year's tax in
total tax over all years, instead of the share of tax in income. That

table year,income*sum  tax*sum  tax*pctsum<year>

I expect I have the denominator specification wrong, but can't find
much information about that - the various tutorials only have a
sentence or two and no examples where the denominator is a sum or
mean. In the past I have always just used proc summary when I needed
the ratio of sums.  Is there a way to do this in tabulate?

Daniel Feenberg
NBER
```
 0

See related articles to this posting

```http://www2.sas.com/proceedings/sugi30/243-30.pdf
you can refer to the above document about proc tabulate.

On Apr 6, 9:58=A0am, feenberg <feenb...@gmail.com> wrote:
> I have heard that I can make proc tabulate calculate the ratio of two
> columns, but I can't figure out how to do it. For example if I want
> the share of tax in income by year and ask for:
>
> =A0 proc tabulate;
> =A0 class year;
> =A0 var income tax;
> =A0 table year,income*sum =A0tax*sum =A0tax*pctsum<year*income>
>
> the first two columns are income and tax by year, as expected, but the
> third column is the column share - the share of that year's tax in
> total tax over all years, instead of the share of tax in income. That
> is, it is the same as if I had asked for:
>
> table year,income*sum =A0tax*sum =A0tax*pctsum<year>
>
> I expect I have the denominator specification wrong, but can't find
> much information about that - the various tutorials only have a
> sentence or two and no examples where the denominator is a sum or
> mean. In the past I have always just used proc summary when I needed
> the ratio of sums. =A0Is there a way to do this in tabulate?
>
> Daniel Feenberg
> NBER

```
 0

1 Replies
892 Views

Similar Articles

12/20/2013 2:11:22 PM
page loaded in 34535 ms. (0)

Similar Artilces:

why the number in all column by using proc tabulate is different with using sum option of proc means ;
Greetings to all, thanks for all who answered my questions yesterday, Now I have a new question, why the total number for all is different with that of using proc means. thanks a lot. Rose proc tabulate data=all order=formatted; class state unmem; var persons; freq persons; table (state all), (unmem all)*(n pctn<unmem all>*f=4.1 ); format unmem unmem. STATE state.; keylabel n='Number' PCTN='Percent'; run; proc means data=all sum; var persons; run; ...

Re: why the number in all column by using proc tabulate is different with using sum option of proc means ;
I believe it has to do with possible missing values in your class statement with the crosstabs. By running a freq on persons, proc tabulate has to have a bucket to throw all people into. If one or more of the buckets are based on missing values, I think sas tosses the persons associated with them. But I'm a little fuzzy on this--it's been a while since I've run into this problem. Hopefully someone will have a more thorough answer, or maybe try the doc chapter on proc tabulate. If this isn't what's happening, post your results, so we all can puzzle over them. HTH, Chi...

sum and count in one Proc tabulate
Hi, I am trying to caculate SUM and COUNT of a Variable with one proc tabulate procedure, so that the SUM of VAR1 will be displayed beside COUNT of VAR1. Example: Type VAR1 A 2 B 1 C 3 D 3 A 3 D 1 The output should be: Type VAR1 Sum COUNT A 5 2 B 1 1 C 3 3 D 4 2 Please let me know if I can use any other procedure to get this out put th...

Proc tabulate sum of several analysis var
When using more than one analysis var how do I get a correct sum ? (not a count of class obs) Is it possible using only proc tabulate ? Sample code proc tabulate data=sashelp.class; class sex; var height weight; table sex all, height weight all; run; What I get ----------------------------------------------------------- | | Height | Weight | All | | |------------+------------+------------| | | Sum | Sum | N | |------------------+-----...

Re: Proc Tabulate SUM and Count #4
This works too .. proc tabulate data=have; class Store ; var ItemAmounts ItemNumbers; table Store,(ItemAmounts='Sum of Item Amounts'*sum=''*F=dollar9.2)(ItemNumbers='Count of'*n=' '*F=6.); run; On Wed, Aug 5, 2009 at 2:31 PM, Shirish Nalavade <shirish.nalavade@gmail.com > wrote: > How about this? > > proc tabulate data=have; > class Store ItemNumbers; > var ItemAmounts; > table Store,(ItemAmounts='Sum of Item > Amounts'*sum=''*F=dollar9.2)(ItemAmounts='Count of'*n=' '*F=6.); > run; > ...

Re: Proc tabulate sum of several analysis var
....also: did you try proc report do do such things? Might be the better choice there. The sum at the rightmost side could be generated as derived column. On Thu, 14 Sep 2006 09:18:27 -0700, ajs2004@BIGFOOT.COM wrote: >You want to add height and weight together? Isn't that meaningless? > >If you _really_ wanted to do that, calculate > > htpluswt = height + weight; > >in a data step, then use that as a third analysis variable in the >table. > >geniz wrote: >> When using more than one analysis var how do I get a correct sum ? (not >> a count of c...

Re: How do I find the sum of mean values in a PROC TABULATE statement? #2
I assume you want a sum of means for the ALL group. You can do this in tabulate by using a special picture format. The problem is that you need to know how many groups you have. Here is an example. The TEST data set is used just to generate some data with class variable, X, with 4 levels. options nocenter; /* a data set to test logic */ data test; do x = 'a','b','c','d'; do i = 1 to 100; a = ranuni(7); output; end; end; proc format; picture smean (round) low - < 0 = '0000009.9999' (prefix='-' mult=4e4) ...

why I can't get weighted numbers by using the proc tabulate
Greetings to All, I want to get a weighted numbers by use the proc tabulate, the persons are weighted numbers, but the result is the observation numbers not the weighted numbers,is something wrong in the below code? thanks in advance. Rose proc tabulate data=aa ; class state unmem; var persons; weight persons; table (state all), (unmem all)*(N*f=8.0 pctn*f=4.1); format unmem unmem.; keylabel n='Number' PCTN='Percent'; run; Hi, Rose, In sas, the keyword N usually means the number of observations (with positive weights, but in proc tabulate, it is just the number of obser...

Re: proc freq wonding if I can get a sum on a variable #2
On Sat, 29 Apr 2006 22:13:03 -0700, David L Cassell <davidlcassell@MSN.COM> wrote: >gscsrc@HOTMAIL.COM wrote: >> >>data descount3; >>set descount2; >>keep contid pcn cnawdamt; >>run; > >First, it's a waste of CPU time and disk space (possibly a HUGE waste if the >data >sets are large) to do this, when you can do the same thing using data set >options and not create a second data set. > >In the part below, change the first line to: > >proc freq data=descount2(keep=contid pcn cnawdamt); > >> proc freq data=descount3...

Re: why I can't get weighted numbers by using the proc tabulate
Hi, Rose, In sas, the keyword N usually means the number of observations (with positive weights, but in proc tabulate, it is just the number of observations unless you specify EXCLNPWGT). You are asking N and PCTN, so those are what you are getting. You can use SUMWGT to get the sum of weights; you can use SUM to get the sum (over observations) of (value multiplied by weight). Look up the OnlineDoc for "Keywords and Formulars." If your weights are integers only, then consider using FREQ statement instead of WEIGHT. If you are using survey data, then consider using specialized soft...