topic Re: How to get yhe difference of the first and last observation in a group? in SAS Programming

How to get yhe difference of the first and last observation in a group?

Shradha1 — Wed, 27 Jan 2021 16:58:01 GMT

I have a data set for students and various time points.
Student time
A 12:10
A 12:11
A 12:20
A 12:45
B 3:10
B 3:16
B 3:32
C 8:05
C 8:54
C 9:10
I want the difference between the first and last observation for a particular student. I want the result as
A 00:35
B 00:22
C 00:55

How do I do this?

Re: How to get yhe difference of the first and last observation in a group?

novinosrin — Wed, 27 Jan 2021 17:12:13 GMT


data have;
input Student $ time :time.;
format time time.;
cards;
A 12:10
A 12:11
A 12:20
A 12:45
B 3:10
B 3:16
B 3:32
C 8:05
C 8:54
C 9:10
;

proc sql;
 create table want as
 select student, range(time) as diff format=time5.
 from have
 group by student;
quit;

Re: How to get yhe difference of the first and last observation in a group?

novinosrin — Wed, 27 Jan 2021 17:14:04 GMT


proc summary data=have nway;
 class student;
 var time;
 output out=want(drop=_:) range=Diff;
 format time time5.;
run;

Re: How to get yhe difference of the first and last observation in a group?

Reeza — Wed, 27 Jan 2021 17:14:43 GMT

Use BY group processing with FIRST and LAST and RETAIN.

data want;
set have;

by Student; *may require presorting to have sorted by student;

*holds value across the rows as you go down;
retain start_time;
*assigns the time to a new variable, start_time;
if first.Student then start_time = time;

*if last record for a student then calculate the duration and output the record;
if last.student then do;
      duration = time - start_time;
      output;
end;

run;

Alternatively, if you know that the smallest time is always the start and the largest time is always the end then you can use the RANGE option from PROC MEANS.

proc means data=have RANGE noprint;
class Student;
var time;
output out=want range = duration;
run;

proc print data=want;
var student duration;
format duration time.;
run;

@Shradha1 wrote:
I have a data set for students and various time points.
Student time
A 12:10
A 12:11
A 12:20
A 12:45
B 3:10
B 3:16
B 3:32
C 8:05
C 8:54
C 9:10
I want the difference between the first and last observation for a particular student. I want the result as
A 00:35
B 00:22
C 00:55

How do I do this?