collapse observations by id (cannot be solved by transpose)

[panda]

Hi - 

Right now my data looks like this:

I want to collapse the data so that each id just has one row, and all the information of time from start005-start009 stop005-stop009 can be preserved. Each id just has one non missing time pair for each time slot. 

I tried sql but this code did not work. 

proc sql;
create table want as
select distinct participantid start005-start009 stop005-stop009 
from data;
quit;

proc transpose did not work neither because there are already multiple vairbles for time.  Does any one has any idea about how to do that?

Thanks!

Manqing 

[kiranv_]

if you can type all the variables below query(add in more variab;les) should work

proc sql;
create table want as select participantid , max(start005) as start005,max(start006) as start006, max(stop005) as stoop05,max(stop009)as stop009
from data

group by participantid;
quit;

[Astounding]

As long as the data set is sorted by ID, you can get there without even knowing the names of the remaining variables:

data want;

update have (obs=0) have;

by id;

run;

[Reeza · Accepted solution]

UPDATE?

data want;

update have(obs=1) have;
by id;
run;

FYI - if you post data as text (preferably a data step) we can test it, otherwise we may not (I won't) because I'm not typing out your data.

[panda]

Thanks!! It works!!

I will upload text format data in the future!!