topic Re: Extract date part from timestamp string in SAS Programming

Extract date part from timestamp string

sascode — Wed, 11 Oct 2023 02:23:39 GMT

Hello, i have a dataset as follows:
data have;
length number $ 54;
input number $;
datalines;
44991.65
run;

This number which is coming as character in SAS, represents in excel

3/6/23 3:29 PM

So the question is, how to extract only the date part from this number?

Re: Extract date part from timestamp string

Tom — Wed, 11 Oct 2023 02:40:54 GMT

Fix your Excel file so that the column with the DATETIME values does not have any cells with character strings. Then the value will come as a numeric DATETIME value.

If you can't fix it then you will have to convert it to a number and add the negative number that represents 30DEC1899 to adjust for the difference in how SAS and Excel number days.

The fraction of a day is ignored by the DATE format, but to remove it you can use the INT() function.

date = int(input(number,32.)) + '30DEC1899'd ;
format date date9.;

Note that the longest string that the normal numeric informat can read is 32 characters, which should be longer than any string you get from Excel that represents an actual datetime value. If you really have some other string that is 54 characters long then that is probably what is causing the actual dates to be converted to strings.

Re: Extract date part from timestamp string

sascode — Wed, 11 Oct 2023 02:55:14 GMT

It worked perfectly. That addition part is the key I was not considering.
Thank you.