Importing xlsx data into SAS changed date variable format

RBERG — Fri, 13 Oct 2023 19:44:20 GMT

I imported an excel file with date information enclosed as a format of mm/dd/yyyy hh:ss (e.g. 08/24/2022 14:09) in Excel. After importing into SAS, the format changed automatically into a series of numbers and variable type is character. I converted numeric variables into dates using datepart and DATE9. format. However, I got the different date for the same record. e.g. 08/24/2022 14:09 changed to 25AUG2082.

I would like to keep the original date information and format. Does anyone know how this can be done? I've tried input/put and datepart functions.

Re: Importing xlsx data into SAS changed date variable format

ballardw — Fri, 13 Oct 2023 20:33:10 GMT

Since you didn't show any of the code that you used to "convert" the values there isn't much to address on that part.

How sure are you of this description: mm/dd/yyyy hh:ss ? I have yet to see a time value used that displayed seconds (SS) without minutes.

Datepart would only work on a DATETIME value and not something that is character, so you are skipping several steps.

The appearance of character values from Import would make one suspect that some of the cells have other text OR that the values in the spreadsheet were a mix of character and numeric date-time values with an Excel format.

One of the most common questions on this forum relates to spreadsheet files and Import having issues because the spreadsheets impose NO restriction on the data values. Import examines a few rows and tries to guess properties, if the data is problematic then the result is problematic. A common solution is to File Save-As the file to a CSV file format and read that. Sometimes examining that CSV with a text editor like Notepad (NOT Excel) will reveal interesting things.

Import that csv. look at the data step code generated by the procedure that appears in the log. It is often a good idea to copy the generated code, paste into the editor and modify it.

Tell SAS to use a large Guessingrows option to examine more rows of the file before guessing what the variable characteristics are.

topic Re: Importing xlsx data into SAS changed date variable format in SAS Programming

Importing xlsx data into SAS changed date variable format

Re: Importing xlsx data into SAS changed date variable format