calculate difference between last and first observation by subject

Frequent Contributor
Posts: 106

calculate difference between last and first observation by subject

Hi,

 id t base value 1 1 5.053078 14.61624 1 5 5.687529 36.09745 1 7 4.149219 45.9342 1 14 3.342423 72.15495 want id t base value 1 13 5.053078 67.10187

So, Basically I am trying to calculate different between first and last t variable value (14-1=13). similarly, difference (67.10) of last observation of value (72.15) with first value of base (5.05).

Any help to achieve this calculation in sas?

Thank you

Super User
Posts: 23,776

Re: calculate difference between last and first observation by subject

Is this by ID or for an entire dataset?

Assuming ID, use BY group and first/last.

``````Data want;
Set have;
By id t;
Retain first_value;

If first.id then
First_value=base;

If last.id then do;
Diff = base - first_value;
OUTPUT; * explicitly only output one record per ID - the last one;
end;

Run;
``````
Posts: 5,541

Re: calculate difference between last and first observation by subject

Get it with:

``````data have;
input id	t	base	value;
datalines;
1	1	5.053078	14.61624
1	5	5.687529	36.09745
1	7	4.149219	45.9342
1	14	3.342423	72.15495
;

data want;
do until(last.id);
set have; by id;
if first.id then do;
firstT = t;
startBase = base;
firstValue = value;
end;
t = t - firstT;
value = value - firstValue;
end;
drop first: base;
rename startBase=base;
run;

proc print data=want noobs; run;``````
PG
Occasional Contributor
Posts: 5

Re: calculate difference between last and first observation by subject

/*Create dataset*/
data create_data;
infile datalines;
input id t base value;
datalines;
1 1 5.053078 14.61624
1 5 5.687529 36.09745
1 7 4.149219 45.9342
1 14 3.342423 72.15495
;
run;

data want;
set create_data end=last;
by id t;

/*Initialize variables*/
retain t_first 0
base1 0
t_first 0
t_last 0
value_last 0
t_diff 0
value_diff 0;

/*Read base and t values for first observation*/
if first.id then do;
base1=base;
t_first=t;
end;

/*Read t and value values from last observation*/
if last.id then do;
t_last=t;
value_last=value;
end;

/*Perform calculation*/
t_diff=t_last - t_first;
value_diff=value_last-base1;

/*Subset the dataset for the last observation*/
if last then output;
run;

proc print data=want noobs;
/*Displaying variables of interest*/
var t_first t_last t_diff base1 value_last value_diff;
run;

Discussion stats
• 3 replies
• 1828 views
• 6 likes
• 4 in conversation