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

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

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

5 REPLIES 5
fredstat
Calcite | Level 5

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

worked for me.

fredstat
Calcite | Level 5

on second thought, my numbers are wrong too.


ballardw
Super User

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;

Cynthia_sas
SAS Super FREQ

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
1239
Calcite | Level 5

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 998 views
  • 0 likes
  • 4 in conversation