sas proc report multiple summary rows

[Daily1]

data cars1;
   set sashelp.cars;
   dummy1 = 1;
   dummy2 = 1;
   dummy3 = 1;
   dummy4 = 1;
   dummy5 = 1;
   dummy6 = 1;
run;

proc report data=cars1 nowd;
   column dummy1 dummy2 dummy3 dummy4 dummy5 dummy6
          origin type msrp invoice;
   define dummy1 / group noprint;
   define dummy2 / group noprint;
   define dummy3 / group noprint;
   define dummy4 / group noprint;
   define dummy5 / group noprint;
   define dummy6 / group noprint;
   define origin / group;
   define type / group;
   define msrp / format=dollar12.;
   define invoice / format=dollar12.;

   break after dummy1 / summarize;
   break after dummy2 / summarize;
   break after dummy3 / summarize;
   break after dummy4 / summarize;
   break after dummy5 / summarize;
   break after dummy6 / summarize;

  /* Calculate subtotals */
   compute invoice;  *Use the last variable on the COLUMN statement;
      if upcase(type) = "HYBRID" then do;
         msrptyp6 + msrp.sum;
         invotyp6 + invoice.sum;
      end;
      else if upcase(type) = "SUV" then do;
         msrptyp5 + msrp.sum;
         invotyp5 + invoice.sum;
      end;
      else if upcase(type) = "SEDAN" then do;
         msrptyp4 + msrp.sum;
         invotyp4 + invoice.sum;
      end;
      else if upcase(type) = "SPORTS" then do;
         msrptyp3 + msrp.sum;
         invotyp3 + invoice.sum;
      end;
      else if upcase(type) = "TRUCK" then do;
         msrptyp2 + msrp.sum;
         invotyp2 + invoice.sum;
      end;
      else if upcase(type) = "WAGON" then do;
         msrptyp1 + msrp.sum;
         invotyp1 + invoice.sum;
      end;
   endcomp;

  /* Reassign to subtotal values */
   compute after dummy6;
      origin = "TOTALS";
      type = "Hybrid";
      msrp.sum = msrptyp6;
      invoice.sum = invotyp6;
   endcomp;
   compute after dummy5;
      type = "SUV";
      msrp.sum = msrptyp5;
      invoice.sum = invotyp5;
   endcomp;
   compute after dummy4;
      type = "Sedan";
      msrp.sum = msrptyp4;
      invoice.sum = invotyp4;
   endcomp;
   compute after dummy3;
      type = "Sports";
      msrp.sum = msrptyp3;
      invoice.sum = invotyp3;
   endcomp;
   compute after dummy2;
      type = "Truck";
      msrp.sum = msrptyp2;
      invoice.sum = invotyp2;
   endcomp;
   compute after dummy1;
      type = "Wagon";
      msrp.sum = msrptyp1;
      invoice.sum = invotyp1;
   endcomp;
run;

Result

I want this type of result

[Ksharp]

Can you explain the logistic of calculation . Why you have three same SUV in Asia ? How to get these number ?

[ballardw · Accepted solution]

You have not described the logic behind totals or the repeated SUV HYBRID very well.

Some of what you may want might be done with a multilabel format based on existing values. Note only a few procedures will use Multilabel formats: Proc Means/summary, Tabulate and Report

proc format;
value $typemlf (multilabel notsorted)
'Hybrid' =' Hybrid'
'SUV'    =' SUV'   
'Sedan'  =' Sedan' 
'Sports' =' Sports'
'Truck'  =' Truck' 
'Wagon'  =' Wagon' 
'Hybrid','Sedan','Sports'='Tot Hyb. Sed.'
'SUV','Truck','Wagon' = 'Tot SUV Truck'
;
run;


proc report data=sashelp.cars nowd ;
   column 
          origin type msrp invoice;
   define origin / group;
   define type / group format= $typemlf. mlf ;
   define msrp / format=dollar12.;
   define invoice / format=dollar12.;

run;

You can even force silly appearance with repeats

proc format;
value $typemlfalt (multilabel notsorted)
'Hybrid' ='   Hybrid'
'SUV'    ='   SUV'   
'Sedan'  ='  Hybrid' 
'Sports' =' Hybrid'
'Truck'  ='  SUV' 
'Wagon'  =' SUV' 
'Hybrid','Sedan','Sports'='Tot Hybid.'
'SUV','Truck','Wagon' = 'Tot SUV'
;
run;


proc report data=sashelp.cars nowd ;
   column 
          origin type msrp invoice;
   define origin / group;
   define type / group format= $typemlfalt. mlf ;
   define msrp / format=dollar12.;
   define invoice / format=dollar12.;

run;

Your example which has "Sedan" in Asia as "Hybrid" and as "SUV" in Europe means you are likely NOT considering the result of not having all of the values of the Type variable in each Origin. A Format consistently  makes the same assignment even when type values are missing for a given Origin.

Note the the MytypeAlt format uses a different number of spaces to create different values  so each value of Type gets grouped similarly but the DISPLAY for the text is default left justified which removes the spaces and make it appear as if the values are the same.

Personally I would hope never to see a report that looks like your "want" as it doesn't make any sense to me. Perhaps this is a case of hiding too much of the actual problem