Struggling with formatting datetime from Excel

[AbhishekT]

Hi,

Can anybody help me with formatting the datetime correctly in my program? I am mostly getting blank columns or getting the date as 01Jan1960:9:25:00 Etc.

Here's the code I am using

data x;
set x;
y_new = input(y, andtdtm.);
format y_new datetime22.;
run; 

And here's how some values in the datetime column look like when imported into SAS:

45348.3506829051
45349.8328009028
45350.7706134028

Thank you!

Best regards,

Abhishek

[New_2_SAS]

Hello There, what's the date look like on your input data and how do you want it to look on your output?

[maguiremq]

There is this post, but I've never used it nor can I verify the accuracy.

[Tom · Accepted solution]

If a column in your worksheet has cells that are numbers (Excel, like SAS, stores dates and datetimes as numbers) and other cells that are character strings then SAS will have to make a character variable to store the date from that column.

When it does that the actual dates are converting into digit strings like what you showed.

The best fix is to remove (clear/delete/set to empty) the cells with character strings in that column. Then SAS will  be able to import the column into a numeric variable.

If you cannot fix it then you will need to fix it in SAS.  So you will need to make a NEW variable to hold the datetime value.  To read those strings into a number just use the normal numeric informat.  Since INPUT() does not care if the wdith on the informat specification used is larger than the length of the string being read just use 32. since that is the maximum width that informat allows.  You need to adjust for the difference in how SAS and Excel start numbering days. And finally convert from days into the seconds that SAS uses to store datetime values.

data x;
  set x;
  y_new = dhms(input(y,32.)+'30dec1899'd,0,0,0);
  format y_new datetime19.;
run; 

[ballardw]

You don't state so but I believe the values shown indicate that you used Proc Import, or widget that calls Proc Import, to read the XLSX file.

Unless you have a very large number of files I suggest:

1) make sure the column(s) with date, time or datetime values have a display format that you like (with 4 digit years just in case).

2) Do a file save as in the spreadsheet software to create a CSV file.

3) import or read that CSV file.

For one thing if you us Proc import on CSV the log will show the statements used to read the file and you can copy and modify that for things that have issues. Like account numbers with significant leading zeroes that get removed can be read into character variables as they should be.

Save that program an you can read other similar CSV files and not have variable names, types, lengths change as will happen with importing XLSX (or XLS) files.

[Kurt_Bremser]

These numbers are the raw values of Excel datetimes. Add '30dec1899'd (a SAS date literal) to them, then multiply by 86400 to get a raw SAS datetime value.

Or save the data to a text (csv) file and read that with a DATA step; create a suitable informat to deal with special values (those which caused PROC IMPORT to go wrong).

[Patrick]

Excel is just a terrible data source....

If you have a header row in your Excel sheet then one way to go would be to read this header row also as data (header=no) which will read all data into SAS character variables (A,B,C...). Then use a 2nd data step (firstobs=2) where you convert what should be numeric into new variables.

If you're dealing with a variety of date/datetime strings in your source data (and the character variables in the first table after import) for which there isn't a single SAS informat then you could use code along the line of below.

if not missing(initial_var) then
  do;
    new_var=input(initial_var,?? <informat_1>);
    if missing(new_var) then new_var=input(initial_var,?? <informat_2>);
    ....and so on until you've exhausted the possibilities....
end;