Changing a string in Excel to a datetime, and making SAS retain the format.

[suztang]

Hi folks, long time listener, first-time caller.

I have an Excel spreadsheet with some date time fields that were stored as regular text. I used Excel to change the format to date and time (mm/dd/yy hh:mm). Then I imported the sheet into SAS. SAS changed all the fields back to character strings.

How do I get SAS to recognize the fields as date and time and format them as datetime18.? I am using Enterprise Guide.

[UPDATE] Despite wanting to throw my computer out several times, I got it sussed out. The Answer is right: Turns out someone screwed up the spreadsheets, so there were frame shifts and wrong things in the wrong columns. I had to sort through thousands of observations to find the errors. Took forever, but we got it!

[Patrick · Accepted solution]

@suztang

There are multiple ways how you can import an Excel into SAS. To give you an answer we would need to know how you've done it. Also the SAS version is of importance (to eventually propose one of the other options).

SAS analyses your Excel before importing. If one of your cells in Excel is still character then SAS likely creates a character variable.

If this is a once off then I personally wouldn't spend too much time with trying to get the generated field type right. Just add another data step where you convert the column to what you need using some code like:

format want_dt <format>;
want_dt=input(source_dt_string, ? <informat>);

[suztang]

I used SAS Enterprise Guide 7.1. The spreadsheet is xlsx from Excel 2013.

Using the Process Flow interface, I clicked on [File] --> [Import Data] and followed the steps to import the file that way.

When I did not modify the formats in the dialogue, the data imported, but as strings. When I did try to modify the format, I lost all the data. All the fields were populated with '.'

Now I have checked this spreadsheet, and every field that I changed is formatted as a date because I did that manually in Excel. Excel treats it as such. But it seems SAS doesn't recognize the change I made to the table.

[Patrick]

@suztang

What you're doing normally work for me. It's almost impossible to figure out what's not working in your case without actually testing it with your spreadsheet. I can't think of anything else than at least one of your cells containing a non-date value so SAS decides to import it as string into a character variable. 

You can use code as I've already posted to post-process the imported data. In doing so SAS should throw a warning for cases where the informat doesn't work (=string not recognized as a date). That should help to narrow down the issue.