topic Re: Calculating the difference between two observation in SAS Programming

Calculating the difference between two observation

Neal3321 — Sat, 23 Sep 2017 23:19:37 GMT

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

Re: Calculating the difference between two observation

Reeza — Sun, 24 Sep 2017 01:07:00 GMT

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:

    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;

_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

Re: Calculating the difference between two observation

PGStats — Sun, 24 Sep 2017 03:54:32 GMT

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;