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
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.
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
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.
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
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.
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.