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

Reply
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: 7,356

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;

Respected Advisor
Posts: 3,124

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: 17,758

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;

Ask a Question
Discussion stats
  • 3 replies
  • 679 views
  • 0 likes
  • 4 in conversation