topic Re: collapse observations by id (cannot be solved by transpose) in SAS Data Management

collapse observations by id (cannot be solved by transpose)

panda — Mon, 26 Jun 2017 19:56:51 GMT

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

Re: collapse observations by id (cannot be solved by transpose)

kiranv_ — Mon, 26 Jun 2017 20:04:21 GMT

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;

Re: collapse observations by id (cannot be solved by transpose)

Astounding — Mon, 26 Jun 2017 20:06:59 GMT

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;

Re: collapse observations by id (cannot be solved by transpose)

Reeza — Mon, 26 Jun 2017 20:08:50 GMT

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.

Re: collapse observations by id (cannot be solved by transpose)

panda — Mon, 26 Jun 2017 20:14:30 GMT

Thanks!! It works!!

I will upload text format data in the future!!