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
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: