Proc Report, summary by group

[MB_Analyst]

I am trying to produce the following table:

		Females		Males
Group	Test	n	%	n	%	Total
Group a	Test 1
	Test 2
Group b	Test 1
	Test 2
Group c	Test 1
	Test 2
Total	Test 1
	Test 2

The closest I have come is only one row for the "total", but I want to show the overall frequency for each test. The code I have now is:

PROC REPORT DATA=have ;
	COLUMNS groups test sex, (n pct) all;
	DEFINE groups / group ;
	DEFINE test / GROUP "test ";
	DEFINE sex/  ' ' across;
	DEFINE pct / "%" computed format=percent8.;
	DEFINE all / "Total" computed;

   /* Sum total number of responses  */
	compute before test ;
		den = _c3_ + _c5_;
	endcomp;

/*	 counter to shade rows*/
   	compute before groups ;
   		I+1;
	ENDCOMP;

	compute groups ;
		if mod(i,2) eq 1
		then call define(_row_, "style", "STYLE=[background=lightgrey]");
	endcomp;

   /* percentage */
	compute pct;
		_c4_ = _c3_ / den;
		_c6_ = _c5_ / den;
	endcomp;


	compute all;
		all = _c3_ + _c5_;
	endcomp;

	rbreak after  / summarize;

	compute after /style={just=r};
		groups = "Total";
		_c4_ = _c3_ / all;
		_c6_ = _c5_ / all;
	endcomp;
RUN;

[unison · Accepted solution]

May not be the cleanest solution but, it goes like this:

• Create 'have2' which is two copies of 'have'
  • the first will be the normal 'have' but with VIEW='DATA'
  • the second of which has GROUPS='All' and VIEW='SMRY'
• Run proc report with additional grouping variable VIEW but define as noprint so that it is not visible

data have;
	set sashelp.class;
	TEST=catx(' ', 'Test', 1+mod(_n_-1, 2));
	GROUPS=catx(' ', 'Group', byte(mod(_n_-1, 3)+65));
	keep name sex test groups;
run;

data have2;
	set have(in=a) have(in=b);

	if b then
		do;
			VIEW='SMRY';
			GROUPS='All';
			output;
		end;
	else
		do;
			VIEW='DATA';
			output;
		end;
run;

PROC REPORT DATA=have2;
	COLUMNS view groups test sex, (n pct) all;
	DEFINE view / group noprint;
	DEFINE groups / group;
	DEFINE test / GROUP "test ";
	DEFINE sex/ ' ' across;
	DEFINE pct / "%" computed format=percent8.;
	DEFINE all / "Total" computed;

	/* Sum total number of responses  */
	compute before test;
		den=_c4_ + _c6_;
	endcomp;

	/*	 counter to shade rows*/
	compute before groups;
		I+1;
	ENDCOMP;
	compute groups;

		if mod(i, 2) eq 1 then
			call define(_row_, "style", "STYLE=[background=lightgrey]");
	endcomp;

	/* percentage */
	compute pct;
		_c5_=_c4_ / den;
		_c7_=_c6_ / den;
	endcomp;
	compute all;
		all=_c4_ + _c6_;
	endcomp;
RUN;

(Not your data so values will be different)