Need help in time format

[apxprdtr10]

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.

[PeterClemmensen]

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

[apxprdtr10]

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.

[Kurt_Bremser]

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.

[Reeza]

What's your variable type and format?

[apxprdtr10]

Type-Numeric

Format-Time.

[Kurt_Bremser]

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".