Help using Base SAS procedures

proc Tabulate doubt

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

proc Tabulate doubt

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

ProductTypeNameTotal PiecesTotal HoursTotal Production
Zips100A1226.00
B1052.00
C5077.14
Subtotal 72145.14
111A3093.33
B2082.50
Subtotal 50172.94
112A1061.67
B551.00
C40410.00
D70323.33
Subtotal 125186.94
Grand Total 247495.04

Accepted Solutions
Solution
‎05-10-2012 06:32 PM
SAS Super FREQ
Posts: 8,742

Re: proc Tabulate doubt

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;


calc_production_report.png

View solution in original post


All Replies
New Contributor
Posts: 2

Re: proc Tabulate doubt

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

worked for me.

New Contributor
Posts: 2

Re: proc Tabulate doubt

on second thought, my numbers are wrong too.


Super User
Posts: 10,483

Re: proc Tabulate doubt

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;

Solution
‎05-10-2012 06:32 PM
SAS Super FREQ
Posts: 8,742

Re: proc Tabulate doubt

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;


calc_production_report.png
Contributor
Posts: 55

Re: proc Tabulate doubt

ThanksSmiley Happy for the code. Its working as per the requirement.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 322 views
  • 0 likes
  • 4 in conversation