applying completerows only to certain group variables

[js5]

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.

[Cynthia_sas]

HI: Unfortunately, COMPLETEROWS applies to ALL the GROUP or ORDER variables. It's not clear to me what you want. Do you want FRONT and REAR to also be displayed for other cars that don't have that type of drivetrain? For example, Hyundai is in Asia and only has Front drivetrain value. Land Rover is in Europe and only has ALL drivetrain value. If you want to see Front and Rear for Land Rover, do you also want to see All and Rear for Hyundai???

You might get farther with TABULATE and using CLASSDATA and EXCLUSIVE.

But I'm not entirely sure of your reason for pre-summarizing the data and adding TOTAL rows.

Cynthia

[js5]

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.

[ballardw]

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.

[js5]

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:

• Exporting proc tabulate output to excel via ods leads to numbers being stored as text. This makes the excel less usable in comparison to when proc report is used
• Is there a way to make proc tabulate not calculate the statistic?, By default sum is computed which necessitates re-applying the format

Thank you!

[Cynthia_sas]

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

[js5]

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.