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.
Hi,
yes, I would like all drivetrain types to be displayed for all car makes - so I would also like to see All and Rear for Huyndai.
The reason I am pre-summarising the data is due to the fact that with my real data I need to apply and additional condition to the total across types, and also due to the fact that I need to calculate frequencies which have a different denominator depending on whether it is the total or an individual type. As far as I am aware there is no way of doing it with a single proc report pass. I also only need some of the totals, not all 8 combinations.
Thanks for the tip, I will try tabulate. My initial experience with reporting was with proc tabulate, but I then switched to proc report as it seemed more flexible. Looks like both have their strengths.
You might look into the PRELOADFMT option for the variables that you want all values for and not use the COMPLETEROWS option.
The Preloadfmt would require you to make a format with the desired values to display and then use the format along with Preloadfmt and order=data in the define statement for the variable.
I was able to almost get the desired results by switching to proc tabulate and doing a data step to generate classdata dataset:
data cars_summary_classdata;
set cars_summary;
by Type Origin Make;
if first.Make then
do DriveTrain="Total", "All", "Front", "Rear";
output;
end;
run;
proc tabulate data=cars_summary order=data classdata=cars_summary_classdata exclusive;
class Type Origin Make DriveTrain / preloadfmt;
var cars;
table Origin*Make*DriveTrain, Type*cars;
format Origin $origin. Make $make. DriveTrain $trivetrain. Type $type.;
run;
Only two issues remain:
Thank you!
Hi:
How did you export the PROC TABULATE to Excel -- using ODS EXCEL or ODS TAGSETS.EXCELXP or ODS CSV? You didn't show any ODS statements in your code. PROC TABULATE always uses a statistic either N for CLASS variables or SUM for numeric variables. So when you have TYPE*CARS, the implicit statistic is as though you had typed TYPE*CARS*SUM.
You can use a style override to impact the format in EXCEL if you are using ODS EXCEL or ODS TAGSETS.EXCELXP. You can't use a style override with ODS CSV.
But I don't observe any numbers stored as text when I use either ODS EXCEL or ODS TAGSETS.EXCELXP, see the example below:
The only difference I see is the treatment of decimal places in the destination output.
Cynthia
Hello,
This code results in numbers being stored as text:
ods excel file="c:\users\&sysuserid\Work FOlders\Desktop\ods_test.xlsx" options(flow="tables");
proc tabulate data=cars_summary order=data classdata=cars_summary_classdata exclusive;
class Type Origin Make DriveTrain / preloadfmt;
var cars;
table Origin*Make*DriveTrain, Type*cars*f=3.;
format Origin $origin. Make $make. DriveTrain $drivetrain. Type $type.;
run;
ods excel close;
In looks like changing the format of the sum is the culprit.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.