SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

collapse observations by id (cannot be solved by transpose)

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

collapse observations by id (cannot be solved by transpose)

Hi - 

 

Right now my data looks like this:

capture.PNG

 

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 


Accepted Solutions
Solution
‎06-26-2017 04:21 PM
Super User
Posts: 19,770

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

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.

View solution in original post


All Replies
PROC Star
Posts: 325

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

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;

Super User
Posts: 5,498

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

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;

Solution
‎06-26-2017 04:21 PM
Super User
Posts: 19,770

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

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.

Contributor
Posts: 30

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

Thanks!! It works!!

 

I will upload text format data in the future!!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 422 views
  • 7 likes
  • 4 in conversation