Converting timestamp to date in excel and SAS import

0
votes

I am trying to extract the date from a date timestamp in excel. I currently have a data file with a mixture of date formats including date only and date timestamps. This is causing me problems as I am importing the data into SAS and it cannot read both the date only and date timestamps under the same column.

I have tried in Excel converting the timestamp to a date using the following formula:

 =DATEVALUE(DAY(E32) & "/" & MONTH(E32) & "/" & YEAR(E32))

This works in excel and converts the date so that they are all formatted the same and therefore gets around the issue of the timestamp. However when I import the data into SAS, I get null values if the day is greater than 12, i.e. it is reading the date as mm/dd/yyyy. For example:

Excel Date SAS Import Date 09/12/2016 09/12/2016 15/12/2016 #VALUE!

I tried to reformat this in excel using the following to see if it would get around the issue:

=DATEVALUE(MONTH(E32) & "/" & DAY(E32) & "/" & YEAR(E32))

But I then get the same SAS error in excel.

Can anyone help suggest a formula to use in excel that will get around this issue or advice on importing the data into SAS?

2
exceldatedatetimesastimestamp
How are you importing it into SAS? What version of SAS do you have? – Quentin
using the proc import statement: proc import DATAFILE= "Data file1.csv" OUT= data DBMS=csv; GETNAMES=YES; run; – amy
Also I am using Base SAS 9.4 – amy
How are your importing it into SAS? If SAS is having trouble reading '15/12/2016' as a valid date I don't blame it. There is no month 15. If you are somehow converting your Excel file to text and then importing it try using yyyy/mm/dd format for your date strings to avoid the m/d versus d/m confusion. – Tom
I am importing the file using the proc import function and my file is in csv format. This function has worked fine before when my data has contained dates though I have never had to reformat a date variable using a function before, which is what I believe to be the issue. I have coded the formula so that it reads in dd/mm/yyyy format but SAS doesn't seem to be reading it that way. – amy

2 Answers

0
votes

It sounds like your Excel data is in DMY format, but SAS is using MDY. You can check SAS by running the following code :

proc options option=datestyle;
run;

If it is MDY, then change it (and if you're in the UK ask your SAS admin to change the default setting)

option datestyle='DMY';

You can also check the locale value, which in the UK will be EN_GB. This value determines the datestyle value used when working with dates.

proc options option=locale;
run;
0
votes

If you asked SAS to import from an XLSX file then it should be able to tell that the column contains dates, independent of which display format you have attached to the cells. Make sure that all of the cells in a single column are the same type of data and use the same display format.

CSV files are not Excel files and so there is no place to put a formula or any metadata about what type of data is in each column. If you use PROC IMPORT to read the CSV file then SAS will have to guess at what type of data each column in the CSV contains. If you are saving an Excel files as a CSV file for later reading into SAS or other software then you should format your date columns using yyyy/mm/dd format in Excel to prevent confusion that can be caused by different defaults for month and day order. Nobody uses YDM order.

Since a CSV file is just a text file if you want complete control over how SAS reads the date strings then just write the data step to read it yourself. You could run PROC IMPORT and then recall the code that it generates and modify it to read your data. You could read the string into a character variable and then write your own statements to convert it using say the INPUT() function.

If the column has some date values and some date time values then you could try using the ANYDTDTE informat to pull just the date part. That informat should properly handle 15/10/2016 even if your LOCALE settings are for US or other locations where dates are normally represented in MDY order and not DMY order.

If your dates are consistently in the DMY order then use DDMMYY informat to prevent the LOCALE setting from causing PROC IMPORT or ANYDTDTE informat to convert 12/10/2016 to December 10th instead of October 12th. But if your text file actually has some rows with dates in month first order and others in day first order then you will really need some extra information to properly tell the difference between December 10th and October 12th.