PROC REPORT overall grouping before stratifying

nd — Mon, 11 Mar 2024 18:45:51 GMT

I want to first group my data by the first variable (borough) and then stratify by the second (sex) using PROC REPORT. Is this possible to do in one block of code? Switching borough and sex shows a grouped borough variable, but then output is sex*borough instead of borough*sex.

option nolabel;
proc report data=freqs headline headskip nowindows ;
column ( borough sex) grps, (WgtN) ;
define borough / group order=internal  ;
define sex / group  order=internal missing   ;
define grps / across nozero order=internal;
define WgtN / analysis;
run;
option label;

Output:

		Group 1	Group 2
borough	sex	WgtN	WgtN
Bronx	.	765,159	258,072
	Male	369,787	105,974
	Female	394,607	152,097
Brooklyn	.	1,462,698	499,786
	Male	703,190	218,493
	Female	759,333	281,079
Manhattan	.	959,070	373,795
	Male	465,047	163,996
	Female	494,023	209,800
Queens	.	1,271,159	512,406
	Male	624,295	232,340
	Female	646,864	279,260
Staten Island	.	261,142	108,673
	Male	121,637	55,695
	Female	139,505	52,978

Want:

		Group 1	Group 2
borough	sex	WgtN	WgtN
Bronx	.	765,159	258,072
Brooklyn	.	1,462,698	499,786
Manhattan	.	959,070	373,795
Queens	.	1,271,159	512,406
Staten Island	.	261,142	108,673
Bronx	Male	369,787	105,974
	Female	394,607	152,097
Brooklyn	Male	703,190	218,493
	Female	759,333	281,079
Manhattan	Male	465,047	163,996
	Female	494,023	209,800
Queens	Male	624,295	232,340
	Female	646,864	279,260
Staten Island	Male	121,637	55,695
	Female	139,505	52,978

Thanks.

Re: PROC REPORT overall grouping before stratifying

ballardw — Mon, 11 Mar 2024 21:52:34 GMT

One of the typical ways to get Proc Report to provide an other than typical display order is to

1) add a variable to your data set whose values will be used to control the order

2) make that variable the first in the column statement

3) on the define for that variable use the Group or Order as normal and add NOPRINT to suppress it from actually appearing the report table output.

Re: PROC REPORT overall grouping before stratifying

nd — Tue, 26 Mar 2024 15:40:45 GMT

The dataset is from ods output from PROC FREQ. I realized that I could use the TABLE variable as the grouping value to sort the data appropriately. Thanks for your suggestion.

topic PROC REPORT overall grouping before stratifying in ODS and Base Reporting

PROC REPORT overall grouping before stratifying

Re: PROC REPORT overall grouping before stratifying

Re: PROC REPORT overall grouping before stratifying