DATA Step, Macro, Functions and more

calculate difference between last and first observation by subject

Reply
Frequent Contributor
Frequent Contributor
Posts: 101

calculate difference between last and first observation by subject

Hi,

 

idtbasevalue
115.05307814.61624
155.68752936.09745
174.14921945.9342
1143.34242372.15495
    
want   
idtbasevalue
1135.05307867.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: 17,831

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;
Respected Advisor
Posts: 4,649

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;

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