BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
panda
Quartz | Level 8

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
kiranv_
Rhodochrosite | Level 12

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
PROC Star

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
Super User

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
Quartz | Level 8

Thanks!! It works!!

 

I will upload text format data in the future!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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