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 |
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.