SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Transposing a category value to an indicator column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Transposing a category value to an indicator column

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. 


Desired_table_ex_screenshot.jpg

Accepted Solutions
Solution
‎09-08-2016 09:08 AM
Super User
Posts: 11,343

Re: Transposing a category value to an indicator column

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

View solution in original post


All Replies
Solution
‎09-08-2016 09:08 AM
Super User
Posts: 11,343

Re: Transposing a category value to an indicator column

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

PROC Star
Posts: 1,167

Re: Transposing a category value to an indicator column

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 281 views
  • 1 like
  • 3 in conversation