BookmarkSubscribeRSS Feed
Mamacle
Calcite | Level 5

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

12 REPLIES 12
MadhuKorni
Quartz | Level 8

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

BrunoMueller
SAS Super FREQ

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

Mamacle
Calcite | Level 5

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.

Mamacle
Calcite | Level 5

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

HarryLiu
Obsidian | Level 7

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

Mamacle
Calcite | Level 5

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.

BrunoMueller
SAS Super FREQ

Carmen

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

Bruno

Mamacle
Calcite | Level 5

Hi Bruno,

That is exactly how my time_L_ has been defined.

Best regards,

Carmen.

Mamacle
Calcite | Level 5

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.

HarryLiu
Obsidian | Level 7

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

Jim_G
Pyrite | Level 9

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;

ndp
Quartz | Level 8 ndp
Quartz | Level 8

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.

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!

What is Bayesian Analysis?

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.

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
  • 12 replies
  • 1886 views
  • 0 likes
  • 6 in conversation