Countdown to and after an event.

New Contributor
Posts: 3

Countdown to and after an event.

kay, here is a total redo of my question.

As you will see from the uploaded file I have a dataset (in this case hourly) and I want to have the missing times included and the value last to be the same as the row beforehand.

to take a breaf sample

RIC          Date               Time     Last


Maersk     5/Jul/2011     13:00     43920

Maersk     5/Jul/2011     14:00     43920

Maersk     6/Jul/2011     7:00     43511

Maersk     6/Jul/2011     8:00     42520


an transform it into

RIC          Date               Time     Last          Time_Length

Maersk     5/Jul/2011     13:00     43920     -18


Maersk     5/Jul/2011     14:00     43920     -17

Maersk     5/Jul/2011     15:00     43920     -16

Maersk     5/Jul/2011     16:00     43920     -15

Maersk     5/Jul/2011     17:00     43920     -14

Maersk     5/Jul/2011     18:00     43920     -13

Maersk     5/Jul/2011     19:00     43920     -12

...Data continues in here but don't want to give you a too big a post.

Maersk     6/Jul/2011     07:00     43511     0

Maersk     6/Jul/2011     08:00     42520     1


So in summary I want to do two things.

For every date (and some dates are puposefully missing) have data for every hour. Some RICs have different start dates and different end dates and different missing dates.

Have a a count down to a set date/time which will be manually inputted (for this dataset 5OCT2011 10:00:00).

I would also need to be able to do the same to data that has data accurate to the minute for 187 different datasets in a similar format.

Kindest Regards.

Frequent Contributor
Posts: 95

Re: Countdown to and after an event.

Posted in reply to Phoenix66

Sort your data by stock and date and assume one record per minute where present.

Create index variable for within group record count.

Artificial is an indicator which will be set to 1 if there is a gap in time.

Keep track of previous record's time and stock price.

data x;

    input stock $ dt : anydtdtm. price;

    format dt datetime.;


A 1/2/2011:9:0:0  5

A 1/2/2011:9:1:0  5.05

A 1/2/2011:9:3:0  5.01

A 1/2/2011:9:7:0  5.10

B 1/2/2011:9:0:0  8.12

B 1/2/2011:9:2:0  8.07

B 1/2/2011:9:5:0  8.25

B 1/2/2011:9:10:0 8.18



proc sort data=x;

    by Stock dt;


data want;

    artificial = 0;

    index = 0;

    do until(last.Stock);

        set x;

        by Stock;

        Lag_dt = LAG(dt);

        Lag_Price = LAG(Price);

            if not(first.Stock) then do;

                Min_Cnt = INTCK('minute',Lag_Dt,Dt);

                if Min_Cnt > 1then do;

                    _price = price;

                    _dt = dt;

                    artificial = 1;

                    price = Lag_Price;

                    do i=1 to Min_Cnt - 1;

                        dt = INTNX('minute',Lag_Dt,i,'beg');

                        index + 1;



                    artificial = 0;

                    price = _price;

                    dt = _dt;


                index + 1;




    keep Stock dt artificial price index;


Respected Advisor
Posts: 3,852

Re: Countdown to and after an event.

Posted in reply to Phoenix66

I can't fully understand your problem without seeing some sample data.  As far as EXPANDing the time series and LOCFing the price you can do it this way.  You will need SAS/ETS.

data stock;

   input time :time. price;

   format time time.;


5:42 .02

5:45 .03



proc expand out=stock2 to=minute method=step;

   id time;


proc print;


Esteemed Advisor
Posts: 5,523

Re: Countdown to and after an event.

Posted in reply to Phoenix66

Hi, expanding on Data_null_'s proposition :

data stock;
   input stock $ time :time. price event;
   format time time.;
A 5:42 .02 0
A 5:45 .03 0
A 5:46 .03 1
A 5:47 .03 0

proc expand data=stock out=stock2 to=minute;
   by stock;
   id time;
   convert price / method=step;
   convert event / method=join transformout=(FLOOR);

proc sql;
create table stock3 as
select S.stock, time, price, event, intck("MINUTE", eventTime, time) as countdown
from stock2  as S left join (select stock, time as eventTime from stock2 where event) as X
on S.stock=X.stock
order by S.stock, time;
drop table stock2;


Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation