showing N (percentage denominator) in proc tabulate column resp.row header

[js5]

Dear community,

I am currently preparing tables and one of the requirements is to display the N - the denominator of the percentage calculation - in the row resp. in the column header:

I have managed to achieve this using a combination of proc format and proc sql to dynamically generate the numbers, but it unfortunately does not work once 3rd dimension of the tables come into play. This is because the totals are different across 3rd dimension classes and format needs to be the same for all 3 dimensions.

I could probably repeat proc tabulate with 2-dimensional tables calls across the 3rd dimension levels, but this does not scale that well and leads to a lot of code duplication. Is there a better way? Thank you.

[Cynthia_sas]

Hi:
In your title you say "column resp. row header" and in the body of your post you say "row resp. in the column header". And then you indicate that you have a page dimension to the table as well -- so do you want the N in the page, the row or the column dimension?

You don't show ANY code or any test data and since your question is not clear, it is hard to visualize what you're getting and what you want.

Without knowing anything about your data or what your desired results are, it is hard to make constructive suggestions. I do know that many times, I see people using macro variables for supplying the N value in column headers or titles.

Cynthia

[js5]

Hi @Cynthia_sas ,

thank you for responding. Sorry for mixing up rows and columns, I sometimes have to put n in the rows and sometimes in columns, this is probably why I got it mixed up.

Here is the code I am currently using:

proc sql noprint;
	select cats("'", Type, "'='", Type, "~nn=", put(count(Model), best3.), "'")
		into :types separated by " "
			from sashelp.cars
				group by Type;
quit;

proc format;
	value $types
		&types;
	value $drivetrains
		"All" = "AWD"
		"Front" = "FWD"
		"Rear" = "RWD";
quit;

ods escapechar="~";

proc tabulate data=sashelp.cars;
	class Origin DriveTrain Type;

	table DriveTrain="", Type=""*(n="" colpctn="");
		format DriveTrain $drivetrains. Type $types.;
quit;

%symdel types;

It works fine for two-dimensional tables. If I change it to

proc sql noprint;
	select cats("'", Type, "'='", Type, "~nn=", put(count(Model), best3.), "'")
		into :types separated by " "
			from sashelp.cars
				group by Type;
quit;

proc format;
	value $types
		&types;
	value $drivetrains
		"All" = "AWD"
		"Front" = "FWD"
		"Rear" = "RWD";
quit;

ods escapechar="~";

proc tabulate data=sashelp.cars;
	class Origin DriveTrain Type;

	table Origin, DriveTrain="", Type=""*(n="" colpctn="");
		format DriveTrain $drivetrains. Type $types.;
quit;

%symdel types;

it no longer does, since the n shown is for the entire population and not for the specific regions which is what I need. I hope this makes my issue clearer. Thank you for your help in advance.

[Cynthia_sas]

Hi: I don't see region in the output -- just Origin. But since you already have the method in the program to make a format with the N, you could do that with the Origin. You'll probably need another SQL step to do a format for Origin.

If this were my report, I would want to see a total at the bottom of each page dimension that showed the numbers for each value of .DriveTrain and then another total column at the far right that showed the overall count. Something like this:

 You already know how to make the format for Origin, you'd just do the same thing you did for making &TYPES. I didn't bother to do it here, since that technique will work. I think adding the ALL to the Row and Column will improve the report.

Cynthia

[js5]

Hi @Cynthia_sas ,

thank you! What I need though is to have different n format for Types across different Origins. So SUV n=25 for Asia, SUV n=10 for Europe and SUV n=25 for USA. In other words, the n value for all, but having it shown in the header.

I have managed to achieve this moving Origin from class to where condition, wrapping the code in a macro and looping through it but this does not look too pretty. I was hoping there is a better way.

[ballardw]

Perhaps the approach is not to use Proc Tabulate or not directly. Often when the report procedures don't do what you want it may mean that you need to do the summary separately, such as in proc means/summary to have all the values you need.