topic Re: Transposing a category value to an indicator column in SAS Data Management

Transposing a category value to an indicator column

bmutell — Fri, 02 Sep 2016 14:50:20 GMT

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.

Re: Transposing a category value to an indicator column

ballardw — Fri, 02 Sep 2016 15:11:28 GMT

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

Re: Transposing a category value to an indicator column

TomKari — Sat, 03 Sep 2016 01:06:13 GMT

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;