topic Re: Need help in time format in SAS Programming

Need help in time format

apxprdtr10 — Wed, 11 Jul 2018 14:32:42 GMT

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.

Re: Need help in time format

PeterClemmensen — Wed, 11 Jul 2018 14:34:31 GMT

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

Re: Need help in time format

apxprdtr10 — Wed, 11 Jul 2018 15:07:36 GMT

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.

Re: Need help in time format

Kurt_Bremser — Wed, 11 Jul 2018 15:44:07 GMT

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.

Re: Need help in time format

Reeza — Wed, 11 Jul 2018 16:45:17 GMT

What's your variable type and format?

Re: Need help in time format

apxprdtr10 — Thu, 12 Jul 2018 03:29:00 GMT

Type-Numeric

Format-Time.

Re: Need help in time format

Kurt_Bremser — Thu, 12 Jul 2018 05:43:25 GMT

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