topic Re: Formatting date-time variables in SAS Programming

Formatting date-time variables

Recep — Thu, 14 Mar 2019 21:58:21 GMT

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 (__/__/__ __:__).

Re: Formatting date-time variables

ballardw — Thu, 14 Mar 2019 22:25:05 GMT

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

Re: Formatting date-time variables

Kurt_Bremser — Fri, 15 Mar 2019 06:55:06 GMT

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.

Re: Formatting date-time variables

Recep — Fri, 15 Mar 2019 16:57:02 GMT

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.