Dear community, I am trying to generate the report which shows all categories for a group variable, even if no events fit the category. I have managed to build a reproducer using sashelp.cars: %macro cars_counter(type_total=, drivetrain_total=, make_total=, origin_total=);
select %if %length(&type_total) GT 0 %then &type_total as ;Type,
%if %length(&origin_total) GT 0 %then &origin_total as ;Origin,
%if %length(&make_total) GT 0 %then &make_total as ; Make,
%if %length(&drivetrain_total) GT 0 %then &drivetrain_total as ; DriveTrain,
count(distinct Model) as cars
from sashelp.cars
group by 1 /* fake group to simplify concatenation */
%if %length(&type_total) EQ 0 %then ,Type;
%if %length(&origin_total) EQ 0 %then ,Origin;
%if %length(&make_total) EQ 0 %then ,Make;
%if %length(&drivetrain_total) EQ 0 %then ,DriveTrain;
%mend cars_counter;
%macro cars_report;
proc sql;
create table cars_summary as
%do i = 1 %to 2;
%do j = 1 %to 2;
%do k = 1 %to 2;
%cars_counter(
type_total=%scan(|"Total", &i, |),
drivetrain_total=%scan(|"Total", &j, |),
make_total=%scan(|"Total", &k, |)
)
union
%end;
%end;
%cars_counter(
type_total=%scan(|"Total", &i, |),
drivetrain_total="Total",
make_total="Total",
origin_total="Total"
)
%if %eval(&i + &j + &k) LT 8 %then union;
%end;
;
%mend cars_report;
%cars_report
proc format;
value $type(notsorted)
"Hybrid" = "Hybrid"
"SUV" = "SUV"
"Sedan" = "Sedan"
"Sports" = "Sports"
"Truck" = "Truck"
"Wagon" = "Wagon"
"Total" = "Total";
value $origin(notsorted default=10)
"Total" = "Total";
value $make(notsorted default=10)
"Total" = "Total";
value $drivetrain(notsorted default=10)
"Total" = "Total"
"All" = "All"
"Front" = "Front"
"Rear" = "Rear";
run;
proc report data=cars_summary;
columns Origin Make DriveTrain Type, cars;
define Origin / group format=$origin. preloadfmt order=data;
define Make / group format=$make. preloadfmt order=data;
define DriveTrain / group format=$drivetrain. preloadfmt order=data;
define Type / "" across format=$type. preloadfmt order=data;
define cars / " ";
quit; If I add completerows to the proc report statement, I get all combinations of origins and makes in addition to all drivetrain versions. What I would like to show all drivetrains for all makes, but only show the makes for the origins which are present in the input dataset. In other words, I would like to have Front and Rear displayed for Land Rover, but without Land Rover being displayed in Asia and USA. I tried experimenting with nozero and exclusive options but without success so far. Thank you for your support in advance.
... View more
