DATA Step, Macro, Functions and more

Need help in time format

Reply
Occasional Contributor
Posts: 12

Need help in time format

Hi all,

I have a data set in which there is a variable named time,

snippets from time variable:

0:00:00
1:00:00
2:00:00
3:00:00
4:00:00
5:00:00
6:00:00
7:00:00
8:00:00
9:00:00
10:00:00
11:00:00
12:00:00
13:00:00
14:00:00
15:00:00
16:00:00
17:00:00
18:00:00
19:00:00
20:00:00
21:00:00
22:00:00
23:00:00

after 23:00:00, in data set it is in repetition form but when i import this data set it will get converted into this pattern:

0:00:00

1:00:00

2:00:00

3:00:00

4:00:00

5:00:00

6:00:00

7:00:00

8:00:00

9:00:00

10:00:00

1100:00

12:00:00

13:00:00

14:00:00

15:00:00

16:00:00

17:00:00

18:00:00

19:00:00

20:00:00

21:00:00

22:00:00

23:00:00

24:00:00

25:00:00 and so on.

Please help me in getting desired result.

PROC Star
Posts: 1,283

Re: Need help in time format

Posted in reply to apxprdtr10

How do you import the data? And how is the data stored?

 

Please provide more information and be more specific. We have no chance of helping you otherwise

Occasional Contributor
Posts: 12

Re: Need help in time format

the above format of time is same as data stored.

and importing as:-

proc import datafile='/folders/myfolders/ab/cs1.xlsx'
out=cs.weather dbms=xlsx replace;
getnames=yes;
sheet=weather;
datarow=2;
guessingrows=1000;
run;

 

Now i think somebody could help me.

 

Super User
Posts: 10,270

Re: Need help in time format

Posted in reply to apxprdtr10

What you see in Excel is not what you have. The Excel time format creates a display of the internal number modulo 86400, while SAS displays hours beyond 24.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 23,754

Re: Need help in time format

Posted in reply to apxprdtr10

What's your variable type and format?

Occasional Contributor
Posts: 12

Re: Need help in time format

Type-Numeric

Format-Time.

Super User
Posts: 10,270

Re: Need help in time format

Posted in reply to apxprdtr10

I looked deeper into that.

 

There are no time values in Excel (or other spreadsheet programs). Excel actually stores times as fractions of days (so everything dealing with time - dates and times - becomes a datetime internally), and the time format just discards everything before the comma.

 

So when you enter a time series in Excel and expand that beyond 23:59:59, you get an internal value of 1.xxxxx. When SAS tries to import that (by multiplying with 86400 to get a proper time value), you get a value beyond 24 hours, which is displayed as 25:, 26: and so on, until you get more than 99 hours, at which point the SAS format will probably overflow and display a series of asterisks.

So, after import, you should do

timevar = mod(timevar,86400);

to get rid of the "hidden dates".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 6 replies
  • 71 views
  • 1 like
  • 4 in conversation