CODEWITHSUNDEEP
sas
AGK1991
AGK1991

Reputation: 11

SAS: Converting a dataset from rows into columns

I have a SAS dataset where each row represent a contact to a doctor for some person ID. Each ID have a different number of contacts (ie. rows). My dataset looks as follows.

enter image description here

What I want to do is end up with a dataset, that looks like

enter image description here

That is for each unique ID I want to have a dataset, where I create columns for each contact number (NUMBER) and what that type of contact is (CONTACT_TYPE), and the columns should be named based on the number of contact. I also want the code to be automatically create columns based on the ID with the largest number of contacts (MAX) i.e. NUMBER&MAX should be my last column.

I have attempted to somehow convert the NUMBER into a macro-variable 'Name' and that then attempted to somehow

%let name = NUMBER

and then used this in a datastep to do something like

NUMBER&name. = CONTACT_TYPE

in a data step. However, this has not worked for me, and is most likely very inefficient I suspect.

Can someone point me in the right direction to solve this?

Thank you in advance for your time.

Regards Alexander

Upvotes: 0

Views: 250

Answers (2)

Tom
Tom

Reputation: 51566

This type of problem is exactly what PROC TRANSPOSE is designed to handle.

proc transpose data=have out=want prefix=NUMBER ;
  by id;
  id number ;
  var contact_type;
run;

Upvotes: 2

DomPazz
DomPazz

Reputation: 12465

This is accomplished with PROC TRANSPOSE.

First, let's create a column with the new column name.

data temp;
set have;
_name_ = catt("NUMBER",number);
run;

Sort the data if it is not already. Skip if the data is sorted.

proc sort data=temp;
by id number;
run;

Then run transpose

proc transpose data=temp out=want;
by id;
var CONTACT_TYPE;
id _NAME_;
run;

Upvotes: 1

Related Questions