I have a dataset that has data in minutes (from 0 to 59 minutes) for few days. The problem is that while I have the date (day/month/year), the time in this dataset consists only in minutes (ranging from 0 to 59 and again). I know that I have 1440 minutes of data every day. Therefore, I computed the following: 1440minutes/60minutes=24 hours. So now I know that I have the 24 hour of data recorded for each day. I need to create a new variable for the hours since I need the complete date and time to merge this dataset with another one by date and time (hours and minutes). I need to assign the first 60 observations the hour 00.00am, from 61 to 120 the hour 01.00am and so on. And once I have this sorted I need to do it for the 10 days of data that I am analysing. I tried different commands but none of them does what I need. The commands I tried for the first part to convert the minutes in hours are the ones below. I tried only for the first 60 observations but none of them worked. Any ideas in how to sort this out? Thank you in advance!
data intra4;
SET intra ;
do obsnum=60 by 1;
IF 0.00>time_L_>=59.00 then hour=1;
end;
run;
data intra5;
SET intra ;
options firstobs=1 obs=60;
0.00<=time_L_<=59.00 then hour=1.00;
hour time8.;
run;
intra9;
SET intra ;
do i=1 to 60;
hours=1;
format hours hour2.;
end;
run;
data intra2;
set intra;
format time_L_ mmss5.;
minutos=minute(time_L_);
run;
data intra10;
SET intra2;
firstobs=1 obs=60;
0=<minutos<=59 then j=1;
j hour2.;
run;
Carmen
If Time_L_ is a variable that consists of minutes data then you can try the below code.
data want;
set have;
hrs = int(Time_L_/60);
run;
or
data want;
set have;
hrs = floor(Time_L_/60);
run
Hi Carmen
I tried to simulate the data based on your description. Find below sample code to creates some test data and creates a time variable. Please have look and tell us whether the sample data looks like yours.
The checkHour is only for cross checking the result, and to generate the data.
Bruno
Hi Bruno,
Thank you very much for your help. I really appreciate it! The code works perfectly well! The only thing is that I need to keep the date, the minutes and the returns together with the hour since I need to merge this database with another database. The only common fields between these two databases are the date (day, month and year) and the time (hour and minutes). So I wonder how I could modify the code in order to keep the rest of the information. Any idea? Thank you very much in advance!
Carmen.
Hi Bruno,
I have just realised that my dates run from 01MAY2011 to 01JUN2012. So I tried to modify your code but it does not work. Any suggestions to this issue?
data TS3;
set TS;
do myDate = first.date_L_ -1 to last.date_L_;
do checkHour = 0 to 23;
do min = 0 to 59;
output;
end;
end;
end;
format myDate date9.;
run;
Thank you very much in advace!
Carmen
It would be great helpful if you could provide a small subset of your data.
Hi HarryLiu,
My data looks like this
date_L_ Time_L_ Return ....
01MAY2012 0.00 0.012
01MAY2012 0.01 0.011
01MAY2012 0.58 0.012
01MAY2012 0.59 0.015
01MAY2012 0.00 0.017
01MAY2012 0.01 0.012
.
.
01MAY2012 0.58 0.012
01MAY2012 0.59 0.014
01MAY2012 0.00 0.018
.
.
02MAY2012 0.00 0.012
02MAY2012 0.01 0.015
02MAY2012 0.00 0.012
.
.
02MAY2012 0.59 0.019
.
.
.
03MAY2012 0.00 0.011
03MAY2012 0.01 0.012
.
.
03MAY2012 0.59 0.012
Best regards,
Carmen.
Carmen
Is your time_L_ value a decimal number, meaning 0.01 is minute 1 and 0.59 is minute 59?
Bruno
Hi Bruno,
That is exactly how my time_L_ has been defined.
Best regards,
Carmen.
My data looks like this
date_L_ Time_L_ Return ....
01MAY2012 0.00 0.012
01MAY2012 0.01 0.011
01MAY2012 0.58 0.012
01MAY2012 0.59 0.015
01MAY2012 0.00 0.017
01MAY2012 0.01 0.012
.
.
01MAY2012 0.58 0.012
01MAY2012 0.59 0.014
01MAY2012 0.00 0.018
.
.
02MAY2012 0.00 0.012
02MAY2012 0.01 0.015
02MAY2012 0.00 0.012
.
.
02MAY2012 0.59 0.019
.
.
.
03MAY2012 0.00 0.011
03MAY2012 0.01 0.012
.
.
03MAY2012 0.59 0.012
Best regards,
Carmen.
Carmen,
I think the biggest trouble of your data is coding Time_L_. As you mentioned it changes from 0 to 59 and repeated again and again and again. You want assign 00:00 am to the first 60 observations (from obs= 1 to 60) and 01:00 am to the next 60 observations (from 0bs=61 to 120) and so on. I think you can take advantage of this pattern and create a new variable by using do loop. The new variable need repeat 60 times of 00:00am followed by 60 times of repeating 01:00 am, and so on (60*24=1440). You can use this new variable to take place of Time_L_.
Best,
Harry
data; format date date10. time time9.;
input date date9. min return;
if date ne ldate then hr=0;
min=min*100; return=return*100;
time=hms(hr,min,0); hr+1;
ldate=lag(date); retain ldate; drop ldate;
cards;
01MAY2012 0.00 0.012
01MAY2012 0.01 0.011
01MAY2012 0.58 0.012
01MAY2012 0.59 0.015
01MAY2012 0.00 0.017
01MAY2012 0.01 0.012
01MAY2012 0.58 0.012
01MAY2012 0.59 0.014
01MAY2012 0.00 0.018
02MAY2012 0.00 0.012
02MAY2012 0.01 0.015
02MAY2012 0.00 0.012
02MAY2012 0.59 0.019
03MAY2012 0.00 0.011
03MAY2012 0.01 0.012
03MAY2012 0.59 0.012
; proc print; run;
Try this:
Data y;
set x;
Cnt=ceil (_n_/1440);
hrs=ceil (_n_/60);
hrs=hrs-(cnt-1)*24;
Run;
Hrs = 1-24 for each date.
Will work if you have 1440 records for each date.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.