Getting Lead Values based on time (Next 1 Minute, Next 5 Minute)

[rkdubey84]

Dear Friends Good Morning.

I have a dataset in the following format:

DTM1                                               BBP

03JUN2013:09:15:00                       164.6

03JUN2013:09:16:00                       167

03JUN2013:09:17:00                       166

03JUN2013:09:18:00                       164.15

03JUN2013:09:19:00                       176.1

03JUN2013:09:20:00                       169.3

03JUN2013:09:21:00                       165

03JUN2013:09:22:00                       180.7

03JUN2013:09:23:00                       170

03JUN2013:09:24:00                       180.3

03JUN2013:09:25:00                       176

I want to have lead values of BBP for 1 minute and 5 minutes. Like the one shown below:

DTM1                                               BBP              BBP1          BBP5

03JUN2013:09:15:00                       164.6            167             169.3

03JUN2013:09:16:00                       167               166             165

03JUN2013:09:17:00                       166               164.15        180.7

03JUN2013:09:18:00                       164.15          176.1          170

03JUN2013:09:19:00                       176.1            169.3          180.3

03JUN2013:09:20:00                       169.3            165             176

03JUN2013:09:21:00                       165               180.7             .

03JUN2013:09:22:00                       180.7            170                .

03JUN2013:09:23:00                       170               180.3             .

03JUN2013:09:24:00                       180.3            176                .

03JUN2013:09:25:00                       176                 .                   .

How to go about it. Plz. Help!!

Thanks in Advance.

Ritesh

[mkeintz]

1. If you're going to SGF, then come attend my presentation:
   Leads and Lags: Static and Dynamic Queues in the SAS® DATA STEP, ed. 2
   
   
2. If there are no holes in your time series, then your 1-mintue and 5-minutes leads translate into 1-record and 5-record leads:
   
     data want;
        merge have    have (firstobs=2 keep=bbp rename=(bbp=bbp1)) have (firstobs=4 keep=bbp rename=(bbp=bbp5));
     run;
   
   I call the above series "regular", becuase the distance between consecutive records is constant
3. If there are holes, what do you want to do?
   1. Keep the highest dtm1 <= 1 or 5 minutes ahead?
   2. Keep the lowest dtm1 >= 1 or 5 minutes ahead?
   3. set bbp1 or bbp5 to missing?
      
      
4.  Do you have by groups?

[rkdubey84 · Accepted solution]

Hi @mkeintz,

Thanks for replying. I think this question has been reposted (allowed) by SAS Community moderator as it was marked spam for some reasons. Another variation of this question was posted earlier where you, @Ksharp, @Kurt_Bremser, & @Reeza had helped me to get the desired results.

Previous Question Link

Here is the solution code that I used based on suggestions....

%Macro MidQuote(T);
Data FD.&T._MQ; Set FD.&T._Final;
Keep DTM1 BBP BSP BBQ BSQ;
DT = Datepart(DTM1);
Format DT Date9.;
Dif = DTM1 - Lag(DTM1);
run;
proc sort data=FD.&T._MQ noduprecs;
by DTM1; 
Run;
proc sql;
create table FD.&T._MQ_Final as
select *,
(select bbp from FD.&T._MQ where dtm1=a.dtm1+'00:01:00't ) as BBP1,
(select bbp from FD.&T._MQ where dtm1=a.dtm1+'00:05:00't ) as BBP5,
(select bsp from FD.&T._MQ where dtm1=a.dtm1+'00:01:00't ) as BSP1,
(select bsp from FD.&T._MQ where dtm1=a.dtm1+'00:05:00't ) as BSP5,
(select bbq from FD.&T._MQ where dtm1=a.dtm1+'00:01:00't ) as BBQ1,
(select bbq from FD.&T._MQ where dtm1=a.dtm1+'00:05:00't ) as BBQ5,
(select bsq from FD.&T._MQ where dtm1=a.dtm1+'00:01:00't ) as BSQ1,
(select bsq from FD.&T._MQ where dtm1=a.dtm1+'00:05:00't ) as BSQ5
from FD.&T._MQ as a;
quit;
%Mend;
%Midquote(ACC);

Thanks a ton @mkeintz for taking out time and helping.

Love to be a part of the SAS Communites. Have a great day ahead.

Ritesh