BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

6 REPLIES 6
Shmuel
Garnet | Level 18

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;
Cynthia_sas
SAS Super FREQ

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

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9
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/...
Cynthia_sas
SAS Super FREQ
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
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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