Solved
Learner
Posts: 1

# Calculating the difference between two observation

I want to calculate that Price at time t minus Price at time t+1 for security i at day k. Particularly, t+1 is defined as the price at least 5 min after the price at time t. Hence, I added an variable to regulate the interval and used following code to create a row of Price at time t+1. Here is the sample of input data.

``````    data test4;
length _ric\$ 25 type\$ 5 interval\$ 15 time_l_\$ 25 ;
input _ric\$ date_l_ time_l_  type\$ price interval\$;
datalines;
AXPA031407800.U   20131212    9:52:56.537 Trade   5.85    09:50:00
AXPA031407800.U 20131212    9:52:56.537 Trade   5.85    09:50:00
AXPA031407800.U 20131212    9:53:13.586 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:53:13.586 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:53:13.607 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:53:13.607 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:53:34.990 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:55:12.990 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:12.990 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:13.002 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:13.002 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:13.002 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:13.011 Trade   5.7 09:55:00
AXPA031407900.U 20131205    9:37:58.420 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
;

data test1;
set test nobs=nobs;
do _i = _n_ to nobs until (new_date ne date_l_ or new_time > interval);
set test (rename=(date_l_=new_date price=new_price interval=new_time)) point=_i;
end;
if (date_l_ ne new_date) or (_i > nobs) then call missing(new_price);
run;``````

The output data is shown as following. However, the _RIC (security name), date_l_, and time_l_ are changed. As shown in output data, for example, AXPA031407800.U has 13 observations in input data but 7 observations in output data. Does anyone know how to solve this problem, or a better way to calculate the difference between P at time t and t+1? Thanks

``````_ric    type    interval    time_l_ date_l_ price   new_date    new_time    new_price
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.85    20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.85    20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00 ``````

Accepted Solutions
Solution
‎09-24-2017 09:41 AM
Posts: 5,483

## Re: Calculating the difference between two observation

Your code is sound. Just a small modif is required:

``````data test1;
set test4 nobs=nobs;
do _i = _n_ to nobs until(other_date > date_l_);
set test4(
rename=(    date_l_= other_date
price = other_price
interval = other_time)
keep=date_l_ price interval )
point=_i;
if other_time > interval and other_date = date_l_ then do;
new_price = other_price;
leave;
end;
end;
drop other_: ;
run;``````
PG

All Replies
Super User
Posts: 23,343

## Re: Calculating the difference between two observation

Do you have a license for SAS ETS? If so, look at PROC EXPAND.

Depending on the size of your data, reversing that calculation is super easy, ie sort descending and then use the LAG or DIF function but they're not optimized.

There's also a trick of merging the data with itself but starting one observation later so they line up and you can do direct subtraction.

``````data want;
merge have1 have1 (firstobs=2);
by ID;
run;
``````

Neal3321 wrote:

I want to calculate that Price at time t minus Price at time t+1 for security i at day k. Particularly, t+1 is defined as the price at least 5 min after the price at time t. Hence, I added an variable to regulate the interval and used following code to create a row of Price at time t+1. Here is the sample of input data.

``````    data test4;
length _ric\$ 25 type\$ 5 interval\$ 15 time_l_\$ 25 ;
input _ric\$ date_l_ time_l_  type\$ price interval\$;
datalines;
AXPA031407800.U   20131212    9:52:56.537 Trade   5.85    09:50:00
AXPA031407800.U 20131212    9:52:56.537 Trade   5.85    09:50:00
AXPA031407800.U 20131212    9:53:13.586 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:53:13.586 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:53:13.607 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:53:13.607 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:53:34.990 Trade   5.8 09:50:00
AXPA031407800.U 20131212    9:55:12.990 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:12.990 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:13.002 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:13.002 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:13.002 Trade   5.7 09:55:00
AXPA031407800.U 20131212    9:55:13.011 Trade   5.7 09:55:00
AXPA031407900.U 20131205    9:37:58.420 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
AXPA031407900.U 20131205    9:39:04.996 Trade   6.25    09:35:00
;

data test1;
set test nobs=nobs;
do _i = _n_ to nobs until (new_date ne date_l_ or new_time > interval);
set test (rename=(date_l_=new_date price=new_price interval=new_time)) point=_i;
end;
if (date_l_ ne new_date) or (_i > nobs) then call missing(new_price);
run;``````

The output data is shown as following. However, the _RIC (security name), date_l_, and time_l_ are changed. As shown in output data, for example, AXPA031407800.U has 13 observations in input data but 7 observations in output data. Does anyone know how to solve this problem, or a better way to calculate the difference between P at time t and t+1? Thanks

``````_ric    type    interval    time_l_ date_l_ price   new_date    new_time    new_price
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.85    20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.85    20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407800.U Trade   09:50:00    9:55:12.990 20131212    5.8 20131212    09:55:00    5.7
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:55:00    9:37:58.420 20131212    5.7 20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00
AXPA031407900.U Trade   09:35:00    9:39:04.996 20131205    6.25    20131205    09:35:00 ``````

Solution
‎09-24-2017 09:41 AM
Posts: 5,483

## Re: Calculating the difference between two observation

Your code is sound. Just a small modif is required:

``````data test1;
set test4 nobs=nobs;
do _i = _n_ to nobs until(other_date > date_l_);
set test4(
rename=(    date_l_= other_date
price = other_price
interval = other_time)
keep=date_l_ price interval )
point=_i;
if other_time > interval and other_date = date_l_ then do;
new_price = other_price;
leave;
end;
end;
drop other_: ;
run;``````
PG
☑ This topic is solved.