DATA Step, Macro, Functions and more

How to correctly calculate the result by using PCTSUM in Proc Tabulate

Accepted Solution Solved
Reply
Contributor LL5
Contributor
Posts: 41
Accepted Solution

How to correctly calculate the result by using PCTSUM in Proc Tabulate

In the below Proc Tabulate, I am dividing the numerator by denominator using pctsum. I want to show the result as 0.3077, 0.1579, 0.4667 and 0.1515 for A, B, C and D respectively, instead of the result shown in the below table. Is there any way to get the correct result? Thanks.

 

DATA TEST;

INPUT STORE$ NUM DENOM ;

CARDS;

A 4 13

B 3 19

C 7 15

D 5 33

;

RUN;

PROC TABULATE DATA=TEST;

CLASS STORE;

VAR NUM DENOM;

TABLE STORE, NUM="RESULT"*PCTSUM<DENOM> * FORMAT=7.2;

RUN;

 RESULT
PctSum
STORE30.77
A
B15.79
C46.67
D15.15

Accepted Solutions
Solution
‎07-08-2017 08:39 PM
SAS Super FREQ
Posts: 8,743

Re: How to correctly calculate the result by using PCTSUM in Proc Tabulate

Hi:

  Another option, in the interest of completeness, would be to use PROC REPORT. When you use PROC REPORT, it does NOT automatically multiply by 100 because you can control the division yourself, like this:

proc report data=test;
  column store num denom result;
  define store / display;
  define num / sum;
  define denom / sum;
  define result / computed f=6.4;
  compute result;
    result = num.sum / denom.sum;
  endcomp;
run;

And, then, if you don't want to see num or denom on the report, you can add the NOPRINT option to the DEFINE statement (after the usage of SUM).

 

 

The output looks like this:

divide_report.png

 

cynthia

View solution in original post


All Replies
Trusted Advisor
Posts: 1,378

Re: How to correctly calculate the result by using PCTSUM in Proc Tabulate

PCTSUM converts the result into PERCENT by multipying it by 100.

 

You can do:

DATA TEST;
INPUT STORE$ NUM DENOM ;
result = num/denom;
CARDS;
A 4 13
B 3 19
C 7 15
D 5 33
;
RUN;
PROC TABULATE DATA=TEST;
CLASS STORE;
VAR NUM DENOM;
TABLE STORE, RESULT* FORMAT=7.2;
RUN;
Solution
‎07-08-2017 08:39 PM
SAS Super FREQ
Posts: 8,743

Re: How to correctly calculate the result by using PCTSUM in Proc Tabulate

Hi:

  Another option, in the interest of completeness, would be to use PROC REPORT. When you use PROC REPORT, it does NOT automatically multiply by 100 because you can control the division yourself, like this:

proc report data=test;
  column store num denom result;
  define store / display;
  define num / sum;
  define denom / sum;
  define result / computed f=6.4;
  compute result;
    result = num.sum / denom.sum;
  endcomp;
run;

And, then, if you don't want to see num or denom on the report, you can add the NOPRINT option to the DEFINE statement (after the usage of SUM).

 

 

The output looks like this:

divide_report.png

 

cynthia

Contributor LL5
Contributor
Posts: 41

Re: How to correctly calculate the result by using PCTSUM in Proc Tabulate

Hi Cynthia, thanks for the alternative solutions! I wonder if I can do the following:
Var result / weight = .01;
I assume it may work fine with either 0.01 in numerator weight or 100 in denominator weight. Below is what I found from the sas website, can you give me some advices? Thanks a lot!

Weight the Results II
You can also use the WEIGHT= option to apply weights specifically to individual variables. You do this by applying weights to individual variables with multiple VAR statements.
This means that you can produce tables where different weights are used for different variables, or one variable is weighted and another is not. Or, by making a copy of your analysis variable, you can show that variable both weighted and unweighted in the same table.

From the SAS site (via Dartmouth edu) - http://morgan.dartmouth.edu/Docs/sas92/support.sas.com/documentation/cdl/en/proc/61895/HTML/default/...
SAS Super FREQ
Posts: 8,743

Re: How to correctly calculate the result by using PCTSUM in Proc Tabulate

Hi, Where do you want to use the WEIGHT= option? PROC REPORT does not use a VAR statement, so my assumption would be that you are using WEIGHT= with either PROC MEANS or TABULATE. TABULATE always multiplies by 100 in the calculation of statistics. I showed you a PROC REPORT solution. With PROC REPORT, there is no need to fiddle with the results.

If you want a TABULATE solution, they try out the VAR statement with TABULATE and see whether it works as you envision.

cynthia
Contributor LL5
Contributor
Posts: 41

Re: How to correctly calculate the result by using PCTSUM in Proc Tabulate

Hi Cynthia, I was trying to use the weight statment in the Proc Tabulate like below. I figured out that I have to add the "weight" in the orginal dataset which is test in my case and apply the weight in the Proc tabualte, then the result would be deflated by 100. Thanks for your helps!

 

DATA TEST;

INPUT STORE$ NUM DENOM WEIGHT;

CARDS;

A 4 13 0.01

B 3 19 0.01

C 7 15 0.01

D 5 33 0.01

;

RUN;

 

 

PROC TABULATE DATA=TEST;

CLASS STORE;

VAR DENOM;

VAR NUM / WEIGHT=WEIGHT;

TABLE STORE, NUM="RESULT"*PCTSUM<DENOM> * FORMAT=7.2;

RUN;

 

 RESULT
PctSum
STORE 
A0.31
B0.16
C0.47
D0.15
SAS Super FREQ
Posts: 8,743

Re: How to correctly calculate the result by using PCTSUM in Proc Tabulate

Hi:
I'm glad you worked it out. Just note that with PROC REPORT you do NOT need to have a WEIGHT variable because PROC REPORT does NOT multiply by 100 in the first place.

cynthia
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 138 views
  • 2 likes
  • 3 in conversation