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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.