topic SCANTIME=YES|NO in SAS Programming

SCANTIME=YES|NO

evp000 — Thu, 08 Mar 2018 19:06:39 GMT

SCANTIME=YES|NO
specifies whether to scan all row values in a date/time column and automatically determine the TIME. format if only time
values exist.
YES specifies that a column with only time values be assigned the TIME8. format.
NO specifies that a column with only time values be assigned the DATE9. format. <----- ?????

This doesn't seem to make sense to me. Is it correct? Why would I want a DATE9. format on a column with only time values?

Thanks for your INPUT.

Re: SCANTIME=YES|NO

PGStats — Thu, 08 Mar 2018 20:35:30 GMT

SCANTIME=NO means that the column will be given a DATETIME. format. This means that if it effectively contains time values, they will appear as 01JAN1960:hh:mm:ss

Re: SCANTIME=YES|NO

SuryaKiran — Thu, 08 Mar 2018 21:17:52 GMT

SCANTIME=YES|NO is used because your excel file may shows time format even though the data is datetime value. In excel if we have a column as time format, even we can add datetime data in that column which shows only time in cell. In this situation when importing into SAS you need to tell SAS about the data in Excel. This may be confusing, let me explain briefly.

Case 1: I have a Excel file with column Time only (see the formula tab for exact value stored in the cell )

Now lets see what happens when we import this file using SCANTIME=NO|YES along with USEDATE=YES|NO.

Case 2: If you have time values in excel that are actually datetime (See the formula tab in excel)

In this file the first 4 values are just time, where as the last value is datetime value. So in this situation you need to use SCANTIME=YES|NO and USEDATE=YES|NO to tell SAS whether you need the time values or Date values for the Excel file.

Conclusion: If you have all the values in excel as TIME then you can read the values as TIME format in SAS or if they are datetime then you can read them into SAS as DATETIME or DATE9.

NOTE: In Excel if the cell shows as time there may be a date value hidden, which you can see in formula tab (see screenshot above).

Hope this explains!

Re: SCANTIME=YES|NO

evp000 — Fri, 09 Mar 2018 18:38:51 GMT

So...

original value scantime format** SAS value appears as

01:00 YES time 3600 (seconds) 01:00

01:00 NO datetime 3600 (seconds) 01JAN1960:01:00:00

And this situation would not occur:

01:00 n/ap date. 0.041(days)

Is this correct? So I guess the confusion comes from the fact that they specified DATE. rather than DATETIME..

**There's confusion on my part about whether this is actually a format or an informat. The SCAN option is telling SAS how to read the data from Excel, right?

Re: SCANTIME=YES|NO

evp000 — Fri, 09 Mar 2018 18:50:19 GMT

Thank you for your elaborate response.

In the 2nd example, we're getting erroneous (made up) dates, when the date isn't specified so that's a case where we'd need to make sure we're only getting the time part.

What I've been doing forever is reading all questionable source data as character and dealing with it in SAS, as opposed to trying to manage an import process that could contain weird values. I still believe that this is the way to go. I want to see what's actually in there, regardless of Excel formatting.

This does help me understand the process though.

Thanks again.