BookmarkSubscribeRSS Feed
dstuder
Obsidian | Level 7

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!

 

2 REPLIES 2
Cynthia_sas
Diamond | Level 26

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

Cynthia_sas_0-1588979114563.png

 

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1047 views
  • 0 likes
  • 3 in conversation