BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rkdubey84
Obsidian | Level 7

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
rkdubey84
Obsidian | Level 7

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

 

 

View solution in original post

2 REPLIES 2
mkeintz
PROC Star
  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?
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
rkdubey84
Obsidian | Level 7

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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