topic Re: proc Tabulate doubt in SAS Procedures

proc Tabulate doubt

1239 — Thu, 10 May 2012 13:12:06 GMT

Hi,

Please find the dataset wherein I need to calculate total production based on product, type and name. I have used PROC TABULATE for the achieving my requirement but production column is getting summed up. However, to calculate Production for Subtotal and Grand Total formula is Total Production = (Total Pieces/Total Hours).

Please help me in achieving this.

data prod;

input Product $ Type $ Name $ pieces hours production;

datalines;

Zips 100 A 12 2 6

Zips 100 B 10 5 2

Zips 100 C 50 7 7.14

Zips 111 A 30 9 3.33

Zips 111 B 20 8 2.5

Zips 112 A 10 6 1.66

Zips 112 B 5 5 1

Zips 112 C 40 4 10

Zips 112 D 70 3 23.33

run;

PROC TABULATE data=prod ;

CLASS product type name ;

var pieces hours production;

TABLE product * type * (name ALL = 'Subtotal' * [style=[backgroundcolor=yellow]])

(all = 'Grand Total' * [style=[backgroundcolor=DARK GREEN font_weight = bold]]),

all = '' * pieces ='Total Pieces' *sum='' hours = 'Total Hours' *sum='' production *sum = ''

/ rts = 60 ;

RUN;

--Desired Output

Product	Type	Name	Total Pieces	Total Hours	Total Production
Zips	100	A	12	2	6.00
		B	10	5	2.00
		C	50	7	7.14
	Subtotal		72	14	5.14
	111	A	30	9	3.33
		B	20	8	2.50
	Subtotal		50	17	2.94
	112	A	10	6	1.67
		B	5	5	1.00
		C	40	4	10.00
		D	70	3	23.33
	Subtotal		125	18	6.94
Grand Total			247	49	5.04

Re: proc Tabulate doubt

fredstat — Thu, 10 May 2012 15:10:59 GMT

replace . . . hours = 'Total Hours' *sum='' production *sum = '' . . . with hours = 'Total Hours' *mean='' production *mean = '' ?

worked for me.

Re: proc Tabulate doubt

fredstat — Thu, 10 May 2012 15:23:57 GMT

on second thought, my numbers are wrong too.

Re: proc Tabulate doubt

ballardw — Thu, 10 May 2012 15:43:55 GMT

fredstat, means of rates do that when the samples are different sizes.

I suspect what 1239 wants is PROC REPORT since TABULATE doesn't allow definition of division the way he's thinking.

However creative use of FORMAT and PCTSUM might work.

Note that the multiplier and digit selectors may need to be modified for some ranges of your results.

proc format library=work;

picture notpercent

low-high = '0009.99' (mult=1)

;

run;

PROC TABULATE data=prod ;

CLASS product type name ;

var pieces hours production;

TABLE product * type * (name ALL = 'Subtotal' * [style=[backgroundcolor=yellow]])

(all = 'Grand Total' * [style=[backgroundcolor=DARK GREEN font_weight = bold]]),

all = '' * pieces =''*( sum='Total Pieces' pctsum<hours>='Production rate'*f=notpercent.) hours = 'Total Hours' *sum=''

/ rts = 60 ;

RUN;

Re: proc Tabulate doubt

Cynthia_sas — Thu, 10 May 2012 22:32:45 GMT

Hi:

PROC REPORT can very easily generate the type of report that is required. For example, the code below produces the attached screen shot. Notice that the "original" production column from the data is summarized -- at the break -- which is what I believe the OP was also getting with TABULATE. However, a simple COMPUTE block would be able to calculate PRODUCTION correctly for every report row, including the BREAK rows (Subtotal) and the RBREAK row (Grand Total).

cynthia

data prod;
** make sure Product and Type are large enough;
** for Subtotal and Grand Total in PROC REPORT;
** break processing because default char size of 8;
** is big enough for Subtotal, but not Grand total;
** or other spellings, such as: Sub-Total or Sub Total;
length Product Type $15;
infile datalines;
input Product $ Type $ Name $ pieces hours production;
return;
datalines;
Zips 100 A 12 2 6
Zips 100 B 10 5 2
Zips 100 C 50 7 7.14
Zips 111 A 30 9 3.33
Zips 111 B 20 8 2.5
Zips 112 A 10 6 1.66
Zips 112 B 5 5 1
Zips 112 C 40 4 10
Zips 112 D 70 3 23.33
;
run;

ods listing close;
ods html file='c:\temp\calc_prod.html' style=sasweb;
proc report data=prod nowd;
title 'Calculate Production Number';
title2 'Really do not need original production variable on report';
title3 'since you can calculate production using a computed column';
column Product Type Name pieces hours
         ("Compare These 2 columns" production compprod);
define product / order;
define type / order;
define name / order;
define pieces / sum f=8.0;
define hours / sum f=8.0;
define production / sum f=8.2 "Original Production Value";
define compprod / computed f=8.2 "Computed Production Value";
compute compprod ;
    compprod = pieces.sum / hours.sum;
endcomp;
compute type;
    if upcase(_break_) = 'TYPE' then do;
      type = 'Subtotal';
      Product = ' ';
    end;
    else if _break_ = '_RBREAK_' then do;
      Product = 'Grand Total';
    end;
endcomp;
break after type / summarize;
rbreak after / summarize;
run;
ods html close;

Re: proc Tabulate doubt

1239 — Mon, 21 May 2012 09:20:02 GMT

Thanks for the code. Its working as per the requirement.