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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.