Transposing a category value to an indicator column

[bmutell]

I'm working on a dataset where each row is a criminal offense. In some cases multiple offenses comprise one incident, so I am interested in looking at which offenses commonly occur together. There are 1.37M rows and 82 unique offenses. I want to create a column for each offense where the value will be the number of times that offense occured in each incident (even just an indicator of 1/0 would be satisfactory). Attached is a mock-up of what I would like to accomplish. I've been working on this project in Base and Enterprise Guide and I have access to any other SAS software that could help. I've considered multiple ways, such as running a macro in PROC SQL or somehow calling the column name (i.e. offense name) and comparing it the value of the offense. Any help is appreciated. Thanks in advance. 

[ballardw · Accepted solution]

Do you need this as a dataset? Either Proc Report or Proc Tabulate would generate a report in that format rather easily.

[TomKari]

This may be getting close to what you want:

data have;

set have;

DummyVar = 1;

run;

proc means data=have nway noprint;

class ID_Num Incident_ID Incident_Seq Offence;

var DummyVar;

ID Date;

output out=Tabulated(drop = _TYPE_ _FREQ_) n()=VarCount;

run;

proc transpose data=Tabulated out=Transposed Prefix=Offence;

by ID_Num Incident_ID Incident_Seq;

ID Offence;

var VarCount;

copy Date;

run;

data Transposed;

set Transposed;

by ID_Num Incident_ID Incident_Seq;

if first.Incident_Seq;

run;