expanding observation by 1 min time interval [SAS]

Prev: residual check for multilevel level model
Next: Hazardratio statement in Proc Phreg

From: db on 19 Apr 2010 04:32

Hi,
I have a dataset that has date, time and price.
It is supposed to give you a whole 24 hr period in 1 minute interval
by date.
But as you can see, the value in the time variables skips.(ex.
00:00:00 ---> 00:03:00)
What I am trying to do is to output all 24 hr period in 1 minute time
interval by date without skipping value in time variable.
ex.
00:00:00
00:01:00
00:02:00
....
23:57:00
23:58:00
23:59:00

I need to stick missing time intervals in the time variable so that
the number of observations in the time variable should be 1440 ( 24
hours * 60 sec) per date.

data test;
input date :mmddyy10. time :time8. price;
cards;

1/2/2009 00:00:00 11010
1/2/2009 00:03:00 11009
1/2/2009 00:04:00 11005
1/2/2009 10:05:00 11005
1/2/2009 10:06:00 11010
1/2/2009 10:07:00 11010
1/2/2009 10:08:00 11010
1/2/2009 10:09:00 11010
1/2/2009 10:10:00 11010
1/2/2009 23:11:00 11029
1/2/2009 23:12:00 11012
1/2/2009 23:15:00 11013
1/2/2009 23:57:00 11017
1/2/2009 23:59:00 11028
1/4/2009 00:00:00 11010
1/4/2009 00:05:00 11029
1/4/2009 10:12:00 11012
1/4/2009 10:13:00 11013
1/4/2009 10:14:00 11017
1/4/2009 10:16:00 11028
1/4/2009 23:55:00 11013
1/4/2009 23:56:00 11017
1/4/2009 23:58:00 11028
;

proc print data = test;
format date mmddyy10. time time8.;
run;

Want Dataset

1/2/2009 00:00:00 11010
1/2/2009 00:01:00 .
1/2/2009 00:03:00 11009
....
....
1/2/2009 23:57:00 11017
1/2/2009 23:58:00 .
1/2/2009 23:59:00 11028
1/4/2009 00:00:00 11010
1/4/2009 00:01:00 .
1/4/2009 00:02:00 .
1/4/2009 00:03:00 .
1/4/2009 00:05:00 11029
.....
.....
1/4/2009 23:55:00 11013
1/4/2009 23:56:00 11017
1/4/2009 23:57:00 .
1/4/2009 23:58:00 11028
1/4/2009 23:59:00 .

From: Chris Jones on 20 Apr 2010 04:02

On 19 Apr, 09:32, db <daronnebonn...(a)gmail.com> wrote:
> Hi,
> I have a dataset that has date, time and price.
> It is supposed to give you a whole 24 hr period in 1 minute interval
> by date.
> But as you can see, the value in the time variables skips.(ex.
> 00:00:00 ---> 00:03:00)
> What I am trying to do is to output all 24 hr period in 1 minute time
> interval by date without skipping value in time variable.
> ex.
> 00:00:00
> 00:01:00
> 00:02:00
> ...
> 23:57:00
> 23:58:00
> 23:59:00
>
> I need to stick missing time intervals in the time variable so that
> the number of observations in the time variable should be 1440 ( 24
> hours * 60 sec) per date.
>
> data test;
> input date :mmddyy10. time :time8. price;
> cards;
>
> 1/2/2009 00:00:00 11010
> 1/2/2009 00:03:00 11009
> 1/2/2009 00:04:00 11005
> 1/2/2009 10:05:00 11005
> 1/2/2009 10:06:00 11010
> 1/2/2009 10:07:00 11010
> 1/2/2009 10:08:00 11010
> 1/2/2009 10:09:00 11010
> 1/2/2009 10:10:00 11010
> 1/2/2009 23:11:00 11029
> 1/2/2009 23:12:00 11012
> 1/2/2009 23:15:00 11013
> 1/2/2009 23:57:00 11017
> 1/2/2009 23:59:00 11028
> 1/4/2009 00:00:00 11010
> 1/4/2009 00:05:00 11029
> 1/4/2009 10:12:00 11012
> 1/4/2009 10:13:00 11013
> 1/4/2009 10:14:00 11017
> 1/4/2009 10:16:00 11028
> 1/4/2009 23:55:00 11013
> 1/4/2009 23:56:00 11017
> 1/4/2009 23:58:00 11028
> ;
>
> proc print data = test;
> format date mmddyy10. time time8.;
> run;
>
> Want Dataset
>
> 1/2/2009 00:00:00 11010
> 1/2/2009 00:01:00 .
> 1/2/2009 00:03:00 11009
> ...
> ...
> 1/2/2009 23:57:00 11017
> 1/2/2009 23:58:00 .
> 1/2/2009 23:59:00 11028
> 1/4/2009 00:00:00 11010
> 1/4/2009 00:01:00 .
> 1/4/2009 00:02:00 .
> 1/4/2009 00:03:00 .
> 1/4/2009 00:05:00 11029
> ....
> ....
> 1/4/2009 23:55:00 11013
> 1/4/2009 23:56:00 11017
> 1/4/2009 23:57:00 .
> 1/4/2009 23:58:00 11028
> 1/4/2009 23:59:00 .

Single datastep solution :

data expand (drop=time price lasttime lastprice rename=(thistime=time
newprice=price)) ;
set test ;
by date ;

retain lasttime lastprice ;

if first.date then do ;
lasttime = 0 ;
end ;

lastprice = price ;

if time > (lasttime+60) then do ;
do thistime = (lasttime+60) to (time-60) by 60 ;
newprice = . ;
output ;
end ;
lasttime = time ;
end ;

thistime = time ;
newprice = lastprice ;
output ;
lasttime = time ;

if last.date and time < (24 * 60 * 60) then do thistime = (time +
60) to (24 * 60 * 60) - 1 by 60 ;
newprice = . ;
output ;
end ;

format thistime time8. ;
run ;

From: Patrick on 19 Apr 2010 07:58

The example below uses variable DT containing SAS datetime values
(instead of 2 variables, 1 with a date value and one with a time
value).

I hope it give you an idea how you could solve your problem.

data have;
format DT datetime21.;
do DT='01Feb2009 00:00:00'dt to '01Feb2009 00:10:00'dt by 60;
var=ceil(ranuni(1)*3);
if var<3 then output;
end;
run;

proc sql;
select distinct min(DT) format=datetime21., max(DT)
format=datetime21.
into :MinDT, :MaxDt
from have
;
quit;

Data AllTimeIntervals;
format DT datetime21.;
do DT="%left(&MinDT)"dt to "%left(&MaxDT)"dt by 60;
output;
end;
run;

proc sql;
select ati.DT, h.var
from have as h full outer join AllTimeIntervals as ati
on h.DT=ati.DT
;
quit;

HTH
Patrick

From: data _null_; on 19 Apr 2010 08:36

On Apr 19, 3:32 am, db <daronnebonn...(a)gmail.com> wrote:
> Hi,
> I have a dataset that has date, time and price.
> It is supposed to give you a whole 24 hr period in 1 minute interval
> by date.
> But as you can see, the value in the time variables skips.(ex.
> 00:00:00 ---> 00:03:00)
> What I am trying to do is to output all 24 hr period in 1 minute time
> interval by date without skipping value in time variable.
> ex.
> 00:00:00
> 00:01:00
> 00:02:00
> ...
> 23:57:00
> 23:58:00
> 23:59:00
>
> I need to stick missing time intervals in the time variable so that
> the number of observations in the time variable should be 1440 ( 24
> hours * 60 sec) per date.
>
> data test;
> input date :mmddyy10. time :time8. price;
> cards;
>
> 1/2/2009 00:00:00 11010
> 1/2/2009 00:03:00 11009
> 1/2/2009 00:04:00 11005
> 1/2/2009 10:05:00 11005
> 1/2/2009 10:06:00 11010
> 1/2/2009 10:07:00 11010
> 1/2/2009 10:08:00 11010
> 1/2/2009 10:09:00 11010
> 1/2/2009 10:10:00 11010
> 1/2/2009 23:11:00 11029
> 1/2/2009 23:12:00 11012
> 1/2/2009 23:15:00 11013
> 1/2/2009 23:57:00 11017
> 1/2/2009 23:59:00 11028
> 1/4/2009 00:00:00 11010
> 1/4/2009 00:05:00 11029
> 1/4/2009 10:12:00 11012
> 1/4/2009 10:13:00 11013
> 1/4/2009 10:14:00 11017
> 1/4/2009 10:16:00 11028
> 1/4/2009 23:55:00 11013
> 1/4/2009 23:56:00 11017
> 1/4/2009 23:58:00 11028
> ;
>
> proc print data = test;
> format date mmddyy10. time time8.;
> run;
>
> Want Dataset
>
> 1/2/2009 00:00:00 11010
> 1/2/2009 00:01:00 .
> 1/2/2009 00:03:00 11009
> ...
> ...
> 1/2/2009 23:57:00 11017
> 1/2/2009 23:58:00 .
> 1/2/2009 23:59:00 11028
> 1/4/2009 00:00:00 11010
> 1/4/2009 00:01:00 .
> 1/4/2009 00:02:00 .
> 1/4/2009 00:03:00 .
> 1/4/2009 00:05:00 11029
> ....
> ....
> 1/4/2009 23:55:00 11013
> 1/4/2009 23:56:00 11017
> 1/4/2009 23:57:00 .
> 1/4/2009 23:58:00 11028
> 1/4/2009 23:59:00 .

I think the "best" method would be PROC EXPAND. I do not have SAS/ETS
so I can't show you that way. I do have SAS/Base and you can achieve
the data you want with PROC SUMMARY. But I gotta as why you think you
need it?

data test;
input date :mmddyy10. time :time8. price;
format date mmddyy10. time time8.;
cards;
1/2/2009 00:00:00 11010
1/2/2009 00:03:00 11009
1/2/2009 00:04:00 11005
1/2/2009 10:05:00 11005
1/2/2009 10:06:00 11010
1/2/2009 10:07:00 11010
1/2/2009 10:08:00 11010
1/2/2009 10:09:00 11010
1/2/2009 10:10:00 11010
1/2/2009 23:11:00 11029
1/2/2009 23:12:00 11012
1/2/2009 23:15:00 11013
1/2/2009 23:57:00 11017
1/2/2009 23:59:00 11028
1/4/2009 00:00:00 11010
1/4/2009 00:05:00 11029
1/4/2009 10:12:00 11012
1/4/2009 10:13:00 11013
1/4/2009 10:14:00 11017
1/4/2009 10:16:00 11028
1/4/2009 23:55:00 11013
1/4/2009 23:56:00 11017
1/4/2009 23:58:00 11028
;;;;
run;
data classdata;
do i = 0 to (24*60)-1;
time = intnx('MIN','00:00't,i);
output;
end;
format time time8.;
drop i;
run;

proc summary data=test classdata=classdata nway;
by date;
class time;
output out=expanded(drop=_:) idgroup(out(price)=);
run;
proc print;
run;

|
Pages: 1
Prev: residual check for multilevel level model
Next: Hazardratio statement in Proc Phreg