topic Re: sort a dataset in New SAS User

sort a dataset

knighsson — Sun, 01 Nov 2020 03:34:15 GMT

Hello, I want to sort my dataset. The original data looks like below: The dataset mainly contains two kind of variables: time of the event, and event (each event has a continuous value). The "time 1" is the time of "event 1", "time 2" is the time of "event 2", and so on. "Time 1" to "time 7" are listed in chronological order, so "time 1" is always earlier than or same to "time 2" because event 1 and 2, or event 2 and 3 and 4, or all 7 events could happen at the same time. Each participant could have at least 1 up to 7 events.

But I want to redesign this dataset in a summary way. I want to plus the value of the events that happened at the same time, and make a table that variables are the 24 hours. So the goal is to make a new dataset looks below:

I have some idea about how to do this, but I don't know how to apply my idea into SAS code.

Step 1: assign each time a continuous value: for example, "time 1" is 6:45, so "new time 1" could be 6X60+45=405, but I don't know how to assign this value on all the time of all participants.

Step 2: combine events' value together when they happened at the same time. But I don't know how to link the time and the event together.

Step 3: put the final event value under the new time variable like "6:00-7:00".

I tried to do this, however, due to my limited knowledge about SAS, I dont know how to achieve this in SAS. So I hope you could help me with this.

Last, after I create the new dataset, I want to know when each participant has the highest value of event during the day. Can I generate a new dataset looks like below:

Thank you very much！

Re: sort a dataset

Kurt_Bremser — Sun, 01 Nov 2020 06:23:58 GMT

First, transpose (PROC TRANSPOSE) to a long layout:

data long;
input participant $ eventno time :time8. value;
format time time8.;
datalines;
1 1 06:45:00 23
1 2 06:45:00 400
1 3 11:00:00 300
;

(shortened example)

It is then easy to group by time or participant.

Please post example data as data steps with datalines (see above example), not as pictures. Pictures can't be used for development and testing.

Re: sort a dataset

knighsson — Sun, 01 Nov 2020 15:32:08 GMT

Thank you! The example data is as follow.
Participant# Event # Time Value
1 1 06:45:00 23
1 2 06:45:00 400
1 3 11:00:00 300
1 4 11:00:00 100
1 5 18:00:00 500
2 1 10:00:00 500
2 2 11:45:00 600
2 3 20:00:00 700

Re: sort a dataset

Kurt_Bremser — Sun, 01 Nov 2020 20:40:32 GMT

From this dataset, you can group by hour in this way:

proc sql;
create table want as
  select
    intnx('hour',time,0,'b') as hour,
    sum(value) as value
  from have_long
  group by hour
;
quit;