Formatting date-time variables

[Recep]

Hello there,

I am having trouble to find the right date/time format for the character variable (or_arrival_event) in the attached SAS data file.

I use the following code to convert the field into a SAS date/time field but even though the code does not yield any ERROR message it produces the wrong date/times:

if or_arrival_event in ('__/__/__ __:__') then or_arrival_event=' ';
or_arrival_datetime=input(or_arrival_event,anydtdtm.);
format or_arrival_datetime datetime15.;

Thanks a lot in advance!

Recep

PS: Originally the data was in Excel and the field was numeric with Excel's date/time formatting. After I import it to SAS via PROC IMPORT the field's type became character probably because of the blank values (__/__/__ __:__).

[ballardw]

Prevention may be easier than correction. Go back to Excel. Make sure the entire column is formatted the same (highlight the column and set the display using the format cells).Save the file as CSV. Use Proc Import with a large value for the guessingrows option. This option examines many more rows of data before assigning the variable properties.

Examine the log. You will find datastep code to read the CSV file. If the informat for the variable is character you can 1) copy the code from the log to the editor and 2) set the desired informat. Save and rerun the data step code. If you have someone entering blank field values as shown you may want to use the ? or ?? informat modifier to prevent some of the invalid data messages that will occur if you actually have _/__/___ type data values. The ?? suppresses both the invalid data message and the display of the input data.

If you have read other spreadsheets of similar format then do the save as CSV, change the name in the INFILE and possibly the name of the output data set.

I have had users with Excel manage to create text values that looked like dates or datetimes that were mixed in with actual Excel dates or date time values. The CSV would likely show the text versions in quotes but the data step will read both correctly (unless the text value represents an invalid date time like 31 November...).

[Recep]

Thanks a lot for your response! I think converting the Excel file to .csv resolved all the problem. Though just in case I used the "guessingrows" options which it helped.

[Kurt_Bremser · Accepted solution]

CSV and data step.

CSV and data step.

CSV and data step.

What I tell you three times is the truth.

Excel files are not suited for a reliable import of data into SAS (or any other table-oriented data processing system) as Excel has no concept of fixed column attributes, and so all software that reads spreadsheets has to guess those attributes.

See Maxims 14, 17, 22, 27, 31.