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
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.
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;
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;
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.
Thanks!! It works!!
I will upload text format data in the future!!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.