topic Re: Group rows in PROC TABULATE in SAS Programming

Group rows in PROC TABULATE

dstuder — Fri, 08 May 2020 08:56:09 GMT

Hello everyone,

I have the following (fake) crime data about offenders:

/* Some fake-data */
DATA offenders;
INPUT id :$12. crime :4. offenderSex :$1. count :3.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
1,110,f,3
2,32,f,1
3,31,m,1
4,113,m,1
5,110,m,1
6,31,m,1
7,31,m,1
8,110,f,2
9,113,m,1
10,31,m,1
11,113,m,1
12,110,f,1
13,32,m,1
14,31,m,1
15,31,m,1
16,31,m,1
17,110,f,2
18,113,m,2
19,31,m,1
20,31,m,1
21,110,m,4
22,32,f,1
23,31,m,1
24,31,m,1
25,110,f,4
26,110,m,1
27,110,m,1
28,110,m,2
29,32,m,1
30,113,f,1
31,32,m,1
32,31,f,1
33,110,m,1
34,32,f,1
35,113,m,2
36,31,m,1
37,113,m,1
38,110,f,1
39,113,u,2
;
RUN;

proc format;
 value crimes 110 = 'Theft'
		      113 = 'Robbery'
			  32 = 'Assault'
              31 = 'Minor assault';
run;

I use the following code to create a crosstable:

proc tabulate;
format crime crimes.;
freq count;
class crime offenderSex;
table crime="Type of crime", offenderSex="Sex of the offender" /misstext="0";
run;

Now, I'd like to group the rows, so that the totals for different categories are shown:

                           SEX
                           m   f
Assault
Minor assault
TOTAL VIOLENT CRIMES
Theft
Robbery
TOTAL PROPERTY CRIMES

I've already tried to create another format for the 'crime' variable, so that I can use "TABLE CATEGORY * crime" within PROC TABULATE. However, then I get it like this, which is not exactly, what I want:

                                      SEX
                                      m   f
VIOLENT CRIME   Assault
                Minor Assault
PROPERTY CRIMES Robbery
                Theft
                Burglary

Can anyone please explain me, how to do this? Thank you!

Re: Group rows in PROC TABULATE

RichardDeVen — Fri, 08 May 2020 19:30:21 GMT

Your answer is in your cross post to https://stackoverflow.com/questions/61673882/group-rows-in-proc-tabulate

Re: Group rows in PROC TABULATE

Cynthia_sas — Fri, 08 May 2020 23:06:03 GMT

Hi, as always, want to show a PROC REPORT approach to this. Output is:

code is:

/* Some fake-data */
** create format first;
proc format;
 value crimes 110 = 'Theft'
		      113 = 'Robbery'
			  32 = 'Assault'
              31 = 'Minor assault';
  value grpf 31,32 = 'Violent Crimes'
             110,113 = 'Property Crimes';
run;

DATA offenders;
  length grpvar $30 charcrime $30;
INPUT id :$12. crime :4. offenderSex :$1. count :3.;
INFORMAT id $12.;
INFILE DATALINES DSD;
grpvar = put(crime,grpf.);
charcrime = put(crime,crimes.);
return;
DATALINES;
...same datalines...
;
RUN;
 
title;
options missing=0;
proc report data=offenders
  style(summary)=Header;
  column grpvar charcrime count,offenderSex;
  define grpvar / group noprint;
  define charcrime / group 'Type of Crime'
         style(column)=Header;
  define offenderSex / across 'Sex of Offender';
  define count / sum ' ';
  break after grpvar/ summarize;
  compute charcrime;
    if upcase(_break_) = 'GRPVAR' then do;
	   if grpvar = 'Property Crimes' then
	      charcrime = 'Total Property Crimes';
	   else if grpvar = 'Violent Crimes' then 
	      charcrime = 'Total Violent Crimes';
    end; 
  endcomp;
run;

This did involve making a few new variables but it made the PROC REPORT easier to code.

Cynthia