topic Re: Getting Lead Values based on time (Next 1 Minute, Next 5 Minute) in SAS Programming

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

rkdubey84 — Fri, 03 Mar 2017 05:51:43 GMT

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

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

mkeintz — Tue, 07 Mar 2017 15:44:33 GMT

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
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
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?
Do you have by groups?

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

rkdubey84 — Wed, 08 Mar 2017 04:30:09 GMT

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