topic Re: calculate difference between last and first observation by subject in SAS Programming

calculate difference between last and first observation by subject

ari — Tue, 20 Sep 2016 08:43:05 GMT

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

Re: calculate difference between last and first observation by subject

Reeza — Tue, 20 Sep 2016 08:52:17 GMT

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;

Re: calculate difference between last and first observation by subject

PGStats — Tue, 20 Sep 2016 16:28:22 GMT

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;

Re: calculate difference between last and first observation by subject

kenedy_yc — Wed, 21 Sep 2016 08:16:33 GMT

/*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;