PROC REPORT - COUNT and PERCENTAGES?

[gibsonsg]

Hi everyone.

I have tried a lot but could not get the results I desired in Proc Report. What I want to do is getting counts and percentages based on segment and class.

Inputs are below;

data have;

length SEGMENT $9 CLASS $2;

input SEGMENT CLASS COUNT;

datalines;

SEGMENT_1 X1 25

SEGMENT_1 X2 26

SEGMENT_1 Y1 42

SEGMENT_1 Y2 25

SEGMENT_2 X1 46

SEGMENT_2 X2 25

SEGMENT_2 Y1 21

SEGMENT_2 Y2 42

SEGMENT_3 X1 37

SEGMENT_3 X2 38

SEGMENT_3 Y1 44

SEGMENT_3 Y2 18

;

run;

Desired Results;

1) Counts

SEGMENT	X1	X2	Y1	Y2	TOTAL
SEGMENT_1	25	26	42	25	118
SEGMENT_2	46	25	21	42	134
SEGMENT_3	37	38	44	18	137
TOTAL	108	89	107	85	389

2) Percentages

SEGMENT	X1	X2	Y1	Y2	PERCENTAGE
SEGMENT_1	21%	22%	36%	21%	100%
SEGMENT_2	34%	19%	16%	31%	100%
SEGMENT_3	27%	28%	32%	13%	100%
PERCENTAGE	28%	23%	28%	22%	100%

Thanks in advance for your help.

[ballardw]

Depending on the specific type of percentage needed sometimes Proc Tabulate is easier than Proc Report.

the example below shows separate tables for the count and percentages as shown plus another table with both present.

data have;
length SEGMENT $9 CLASS $2;
input SEGMENT CLASS COUNT;
datalines;
SEGMENT_1 X1 25
SEGMENT_1 X2 26
SEGMENT_1 Y1 42
SEGMENT_1 Y2 25
SEGMENT_2 X1 46
SEGMENT_2 X2 25
SEGMENT_2 Y1 21
SEGMENT_2 Y2 42
SEGMENT_3 X1 37
SEGMENT_3 X2 38
SEGMENT_3 Y1 44
SEGMENT_3 Y2 18
;
run;

proc tabulate data=have;
   class segment class;
   freq count;
   table segment='' all='Total',
         (class='' all='Total')*n=''
         /box=segment
   ;
   table segment='' all='Percentage',
         (class='' all='Percentage')*rowpctn=''*f=best3.
         /box=segment
   ;
   table segment='' all='Total',
         (class='' all='Total')*(n='Count' rowpctn='%'*f=best3.)
         /box=segment
   ;
 
run;

Tabulate by default shows percentagess without a % and have the values as multiplied by 100 and shows 2 decimal places. If you want the % character a custom format would be needed with your data.

[PaigeMiller]

PROC SUMMARY will give you a table that has everything you need to get your desired output #1, but there's a little trick needed.

A simple data step will then allow you to obtain the percentages and then output #2.

proc summary data=have;
	class segment class;
	var count;
	output out=sums sum=;
run;

data sums; /* This is the trick that allows totals to appear */
	length class $ 4;
	set sums;
	if missing(segment) then segment='ZZZZ';
	if missing(class) then class='ZZZZ';
run;

proc format;
	value $segf 'ZZZZ'='Total';
	value $classf 'ZZZZ'='Total';
run;

proc report data=sums;
    columns segment class,count;
    define segment/group format=$segf9. order=internal;
    define class/across format=$classf. order=internal;
    define count/analysis sum;
run;