topic Re: Duplicate values proc transpose in SAS Procedures

Duplicate values proc transpose

Walternate — Fri, 04 Oct 2013 19:43:38 GMT

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.

Re: Duplicate values proc transpose

ballardw — Fri, 04 Oct 2013 21:53:33 GMT

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.

Re: Duplicate values proc transpose

Jagadishkatam — Sat, 05 Oct 2013 05:37:32 GMT

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