DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

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

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

 

 

 


Accepted Solutions
Solution
‎03-08-2017 03:14 AM
Contributor
Posts: 36

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

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@KurtBremser, & @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

 

 

View solution in original post


All Replies
Valued Guide
Posts: 797

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

  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?
Solution
‎03-08-2017 03:14 AM
Contributor
Posts: 36

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

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@KurtBremser, & @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

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 112 views
  • 1 like
  • 2 in conversation