Help using Base SAS procedures

return over a 5 minute/ second period

Reply
New Contributor
Posts: 3

return over a 5 minute/ second period

I am trying to find the return over a 5 minute period by using proc expand in SAS. I am working on financial time series data. The time data is in hr:min:sec. Sample data is given below:

date Co time price vol

01JAN2012 ABC 9:55:59 166.00 100

01JAN2012 ABC 9:56:02 165.40 50

01JAN2012 ABC 9:56:02 165.40 25

01JAN2012 ABC 9:56:03 166.45 700

01JAN2012 ABC 9:56:03 166.80 20

01JAN2012 ABC 9:56:03 167.00 280

01JAN2012 ABC 9:56:06 166.45 1

01JAN2012 ABC 9:56:06 166.05 24

01JAN2012 ABC 9:56:15 166.50 500

01JAN2012 ABC 9:56:16 166.50 25

01JAN2012 ABC 9:56:17 166.50 475

01JAN2012 ABC 9:56:22 166.25 10

01JAN2012 ABC 9:56:26 166.05 50

01JAN2012 ABC 9:56:30 166.25 20

01JAN2012 ABC 9:56:38 166.05 126

01JAN2012 ABC 9:56:40 166.00 100

01JAN2012 ABC 9:56:40 166.00 1

01JAN2012 ABC 9:56:40 166.00 1

01JAN2012 ABC 9:56:43 166.00 1

01JAN2012 ABC 9:56:46 166.00 2

But the output throws up 'time' as 0,1,2,3.... when I run the following code:

data temp;set alldata_sas7bdat; proc expand data=temp out=lagtst method=none; convert time=time_lag1 / transform=(lag 1); convert price=price_lag1 / transform=(lag 1); by date; run; 

proc print data=lagtst (obs=5000); run;

proc delete data=temp; data lagtst_2;set lagtst; timegap = time-time_lag1; if timegap > 0 and timegap < 5 then return=log(price/price_lag1); else return=.; run; 

While I have tried to calculate return over a '5 minute' period, the data I have posted here is not enough. I can provide more data but I wonder if it would be too cumbersome here. So please suggest a method for finding out return of a '5 second' period. I will then make changes to find return over a 5-minute period. Thanks.

Super User
Posts: 10,041

Re: return over a 5 minute/ second period

Posted in reply to lakshmanmv

You can also achieve it by using sql. Here is for 5-seconds period .

data have;
input date :date9. Co $ time : time9. price vol     ;
format date date9. time time.;
cards;
01JAN2012 ABC 9:55:59 166.00 100
01JAN2012 ABC 9:56:02 165.40 50
01JAN2012 ABC 9:56:02 165.40 25
01JAN2012 ABC 9:56:03 166.45 700
01JAN2012 ABC 9:56:03 166.80 20
01JAN2012 ABC 9:56:03 167.00 280
01JAN2012 ABC 9:56:06 166.45 1
01JAN2012 ABC 9:56:06 166.05 24
01JAN2012 ABC 9:56:15 166.50 500
01JAN2012 ABC 9:56:16 166.50 25
01JAN2012 ABC 9:56:17 166.50 475
01JAN2012 ABC 9:56:22 166.25 10
01JAN2012 ABC 9:56:26 166.05 50
01JAN2012 ABC 9:56:30 166.25 20
01JAN2012 ABC 9:56:38 166.05 126
01JAN2012 ABC 9:56:40 166.00 100
01JAN2012 ABC 9:56:40 166.00 1
01JAN2012 ABC 9:56:40 166.00 1
01JAN2012 ABC 9:56:43 166.00 1
01JAN2012 ABC 9:56:46 166.00 2
;
run;
proc sql;
create table want as
 select *,log(price/(select price from have where date=a.date and time+5=a.time)) as return
  from have as a;
quit;

Xia Keshan

New Contributor
Posts: 3

Re: return over a 5 minute/ second period

Thank you very much..

However, the program gives a blank 'return'.

date               stk      time        price        vol     return

01JAN2003    ABC    9:55:59    166.00       100        .

I have used the program giving the path to the datafiles.


libname in 'c:\Users\VLM\Stk data';

data temp;set in.ABC_sas7bdat;

      run;

proc sql;

create table want as

select *,log(price/(select price from temp where date=temp.date and time+5=temp.time)) as return

  from temp;

quit;

Super User
Posts: 10,041

Re: return over a 5 minute/ second period

Posted in reply to lakshmanmv

That is because there is no price at that time . haikuo bian has already pointed out it .

I make a TEMP dataset to hold every second price value.

data have;
input date :date9. Co $ time : time9. price vol     ;
format date date9. time time.;
cards;
01JAN2012 ABC 9:55:59 166.00 100
01JAN2012 ABC 9:56:02 165.40 50
01JAN2012 ABC 9:56:02 165.40 25
01JAN2012 ABC 9:56:03 166.45 700
01JAN2012 ABC 9:56:03 166.80 20
01JAN2012 ABC 9:56:03 167.00 280
01JAN2012 ABC 9:56:06 166.45 1
01JAN2012 ABC 9:56:06 166.05 24
01JAN2012 ABC 9:56:15 166.50 500
01JAN2012 ABC 9:56:16 166.50 25
01JAN2012 ABC 9:56:17 166.50 475
01JAN2012 ABC 9:56:22 166.25 10
01JAN2012 ABC 9:56:26 166.05 50
01JAN2012 ABC 9:56:30 166.25 20
01JAN2012 ABC 9:56:38 166.05 126
01JAN2012 ABC 9:56:40 166.00 100
01JAN2012 ABC 9:56:40 166.00 1
01JAN2012 ABC 9:56:40 166.00 1
01JAN2012 ABC 9:56:43 166.00 1
01JAN2012 ABC 9:56:46 166.00 2
;
run;
data temp;
 merge have have(firstobs=2 keep=date time rename=(date=_date time=_time));
 output;
 if date eq _date then do;
  do i=time+1 to _time-1;
   time=i;output;
  end;
end;
run;


proc sql;
create table want as
 select *,log(price/(select price from temp where date=a.date and time+5=a.time)) as return
  from have as a;
quit;

Xia Keshan

Respected Advisor
Posts: 3,156

Re: return over a 5 minute/ second period

Posted in reply to lakshmanmv

's code assumes that you will have one unique data entry per second, obviously it is not the case, but the logic of his code still holds:

data have;

     input date :date9. Co $ time : time9. price vol;

     format date date9. time time.;

     cards;

01JAN2012 ABC 9:55:59 166.00 100

01JAN2012 ABC 9:56:02 165.40 50

01JAN2012 ABC 9:56:02 165.40 25

01JAN2012 ABC 9:56:03 166.45 700

01JAN2012 ABC 9:56:03 166.80 20

01JAN2012 ABC 9:56:03 167.00 280

01JAN2012 ABC 9:56:06 166.45 1

01JAN2012 ABC 9:56:06 166.05 24

01JAN2012 ABC 9:56:15 166.50 500

01JAN2012 ABC 9:56:16 166.50 25

01JAN2012 ABC 9:56:17 166.50 475

01JAN2012 ABC 9:56:22 166.25 10

01JAN2012 ABC 9:56:26 166.05 50

01JAN2012 ABC 9:56:30 166.25 20

01JAN2012 ABC 9:56:38 166.05 126

01JAN2012 ABC 9:56:40 166.00 100

01JAN2012 ABC 9:56:40 166.00 1

01JAN2012 ABC 9:56:40 166.00 1

01JAN2012 ABC 9:56:43 166.00 1

01JAN2012 ABC 9:56:46 166.00 2

;

run;

proc sql;

     create table want as

           select *,log(price/(select avg(price) from (select price from have where date=a.date and co=a.co and time between a.time and a.time-5 group by co, date having time=min(time) ))) as return

                from have as a;

quit;


Above code takes mean value if you have multiple "price" for the same "second", alternatively, you can also choose to use Max or Min price.


Haikuo

Ask a Question
Discussion stats
  • 4 replies
  • 320 views
  • 4 likes
  • 3 in conversation