## how to calculate last observation minus first observation in data step/ sql

Frequent Contributor
Posts: 122

# how to calculate last observation minus first observation in data step/ sql

I wonder if there is an easy way to create/calculate a variable using first and last observations. For example, see the data set below, the diff variable is what I want to obtain.

firmid          month          return               diff

1                    1               0.1               0.5-0.1=0.4

1                    2               0.1               0.5-0.1=0.4

1                    3               0.5               0.5-0.1=0.4

2                    2               0.2               0.6-0.2=0.4

2                    3               0.4               0.6-0.2=0.4

2                    5               0.6               0.6-0.2=0.4

....

Thanks.

PROC Star
Posts: 8,165

## Re: how to calculate last observation minus first observation in data step/ sql

Easiest with a datastep:

data want (drop=x y);

do until (last.firmid);

set have;

by firmid;

if first.firmid then x=return;

else if last.firmid then y=return;

end;

do until (last.firmid);

set have;

by firmid;

diff=x-y;

output;

end;

run;

Posts: 3,167

## Re: how to calculate last observation minus first observation in data step/ sql

I have no doubt that 's data step is the more appropriate in term of robustness and efficiency. Since you mentioned SQL, so here is one only for FWIW:

data have;

input firmid          month          return;

cards;

1                    1               0.1

1                    2               0.1

1                    3               0.5

2                    2               0.2

2                    3               0.4

2                    5               0.6

;

proc sql;

create table want as

select *, (select return from have where a.firmid=firmid group by firmid having month=max(month))-(select return from have where firmid=a.firmid group by firmid having month=min(month)) as diff

from have a;

quit;

Regards,

Haikuo

Super User
Posts: 23,771

## Re: how to calculate last observation minus first observation in data step/ sql

If you're a beginner at SAS and don't want to dive into DOW loops I suggest a two step process with a merge, the first step is below.

data min_max;

set have;

by firmed month;

retain min_value;

if first.firmid then min_value=return;

if last.firmid then do;

diff=return-min_value;

max_value=return;

output;

end;

keep firmid min_value max_value diff;

run;

Discussion stats
• 3 replies
• 1204 views
• 0 likes
• 4 in conversation