BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Neal3321
Fluorite | Level 6

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

2 REPLIES 2
Reeza
Super User

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 

 


 

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 637 views
  • 0 likes
  • 3 in conversation