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
SAS Super FREQ

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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