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 | |
STORE | 30.77 |
A | |
B | 15.79 |
C | 46.67 |
D | 15.15 |
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:
cynthia
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;
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:
cynthia
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 | |
A | 0.31 |
B | 0.16 |
C | 0.47 |
D | 0.15 |
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.