Help using Base SAS procedures

Duplicate values proc transpose

Reply
Frequent Contributor
Posts: 138

Duplicate values proc transpose

I have a dataset composed of a number of events. Each row represents one event for one person as follows:

ID       Event         Eventdate

a         xyz            04/2012

b         xyz            05/2013

a         efg             08/2012

b         xyz            09/2012

What I'd like to do is look at a person's sequence of events. I was thinking I would transpose the data like this:

proc transpose data=data out=new;

by id;

id event;

var eventdate;

run;

That way, each person will have a sequence of dates and I will know which event is which. My question is what will happen if the same person has the same event twice, and if there's a way to tweak my code to avoid losing data.

I'm also open to suggestions on non-proc transpose ways to establish a sequence of events for each person.

Help is much appreciated.

Super User
Posts: 11,343

Re: Duplicate values proc transpose

Posted in reply to Walternate

What are you going to look at later?

You may want to start by simple sorting by id and eventdate at first. It will be significantly easier to find event x immediately preceded by event y or time between event x than trying to search across a single row.

With a transpose approach you may end up with a larger than expected number of variables when some people have large numbers of events.

Trusted Advisor
Posts: 1,137

Re: Duplicate values proc transpose

Posted in reply to Walternate

Hi, Hope this helps

data have;

    input id$ event$ eventdate$;

cards;

a         xyz            04/2012

b         xyz            05/2013

a         efg            08/2012

b         xyz            09/2012

;

run;

proc sort data=have;

    by id event;

run;

data have_;

    set have;

    retain seq;

    by id event;

    if first.event then seq=1;

    else seq+1;

run;

proc sort data=have_;

    by id seq;

run;

proc transpose data=have_ out=trans_data(drop=seq _name_);

    by id seq;

    id event;

    var eventdate;

run;

Thanks,

Jagadish

Thanks,
Jag
Ask a Question
Discussion stats
  • 2 replies
  • 248 views
  • 0 likes
  • 3 in conversation