topic Re: minute by minute data in hours in SAS Procedures

minute by minute data in hours

Mamacle — Wed, 08 Jul 2015 08:14:49 GMT

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

Re: minute by minute data in hours

MadhuKorni — Wed, 08 Jul 2015 12:03:26 GMT

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;

data want;

set have;

hrs = floor(Time_L_/60);

run

Re: minute by minute data in hours

BrunoMueller — Wed, 08 Jul 2015 14:38:32 GMT

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.

data have;
   do myDate = today() -1 to today();
      do checkHour = 0 to 23; 
         do min = 0 to 59; 
            output; 
         end; 
      end; 

   end; 
   format myDate date9.; 
run; 

data want;
   set have;
   by myDate;

   if first.myDate = 1 then do; 
      newMin = -1; 
   end; 
   newMin + 1; 

   time = hms(0, newMin, 0);
   format time time8.; 
run; 

Bruno

Re: minute by minute data in hours

Mamacle — Wed, 08 Jul 2015 16:58:42 GMT

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.

Re: minute by minute data in hours

Mamacle — Wed, 08 Jul 2015 17:16:46 GMT

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;

format myDate date9.;

run;

Thank you very much in advace!

Carmen

Re: minute by minute data in hours

HarryLiu — Wed, 08 Jul 2015 17:25:09 GMT

It would be great helpful if you could provide a small subset of your data.

Re: minute by minute data in hours

Mamacle — Wed, 08 Jul 2015 21:07:06 GMT

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.

Re: minute by minute data in hours

BrunoMueller — Thu, 09 Jul 2015 05:54:49 GMT

Carmen

Is your time_L_ value a decimal number, meaning 0.01 is minute 1 and 0.59 is minute 59?

Bruno

Re: minute by minute data in hours

Mamacle — Thu, 09 Jul 2015 08:04:18 GMT

Hi Bruno,

That is exactly how my time_L_ has been defined.

Best regards,

Carmen.

Re: minute by minute data in hours

Mamacle — Thu, 09 Jul 2015 10:19:23 GMT

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.

Re: minute by minute data in hours

HarryLiu — Thu, 09 Jul 2015 15:17:45 GMT

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

Re: minute by minute data in hours

Jim_G — Thu, 09 Jul 2015 16:20:57 GMT

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;

Re: minute by minute data in hours

ndp — Thu, 09 Jul 2015 18:38:38 GMT

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.