topic Re: How to convert character datetime to numeric datetime? in SAS Programming

How to convert character datetime to numeric datetime?

Vortex — Mon, 14 Jul 2014 02:24:20 GMT

I have a file in Excel that I use to import the data into SAS.
The format of the data in the Excel is this:
28-05-2014 10:21:20
27-05-2014 14:22:26
27-05-2014 11:36:33
27-05-2014 17:14:55
27-05-2014 10:34:56
27-05-2014 19:03:48
27-05-2014 14:30:56
In SAS, the type is text, length is 19 and format is $19.
I am using this column to admission date which looks like this 08JUL2013:08:22:00. Therefore I would like to convert the above datetime to this format 28MAY2014:10:21:20 or 28MAY2014:10:21:00
Please let me know what I should do?
THank you

Re: How to convert character datetime to numeric datetime?

Reeza — Mon, 14 Jul 2014 03:50:07 GMT

Ideally you should fix it in the import step, perhaps by using usedate=yes and mixed=yes in your proc import step.

If not then you can convert it after importing it by using something like the following:

new_date=input(date, anydtdtm.)

Re: How to convert character datetime to numeric datetime?

sas_lak — Mon, 14 Jul 2014 11:54:24 GMT

data date_formats;

input date $50.;

cards;

28-05-2014:10:21:20

27-05-2014:14:22:26

27-05-2014:11:36:33

27-05-2014:17:14:55

27-05-2014:10:34:56

27-05-2014:19:03:48

27-05-2014:14:30:56

;

run;

data out;
set date_formats;
new_date=input(date,anydtdtm.); /*converting character to date format */
format new_date datetime18.; /* Printing the data into wanted format */
run;

Re: How to convert character datetime to numeric datetime?

Peter_C — Mon, 14 Jul 2014 13:17:38 GMT

And if you want a more specific informat to handle that timestamp layout, try

FINDFDT19.

For a more general search method (than asking onlin) have a look at paper

http://support.sas.com/resources/papers/proceedings14/1744-2014.pdf

Titled VFORMAT lets SAS(R) Do the Format Searching

where this informat is one of the results of a search

Re: How to convert character datetime to numeric datetime?

user24feb — Mon, 14 Jul 2014 15:29:30 GMT

another try

(can't see whether you have 2 variables or 1, but) use DHMS:

Data A;
Input Text_DT $19.;
Datalines;
28-05-2014 10:21:20
27-05-2014 14:22:26
27-05-2014 11:36:33
27-05-2014 17:14:55
27-05-2014 10:34:56
27-05-2014 19:03:48
27-05-2014 14:30:56
;
Run;

Data B;
Set A;
DT=DHMS(Input(Scan(Text_Dt,1,' '),DDMMYY10.),Scan(Scan(Text_DT,2,' '),1,':'),Scan(Scan(Text_DT,2,' '),2,':'),Scan(Scan(Text_DT,2,' '),3,':'));
Format DT DateTime19.;
Run;

Re: How to convert character datetime to numeric datetime?

Vortex — Tue, 15 Jul 2014 14:47:35 GMT

Hey thank you the one by user24feb works.

Re: How to convert character datetime to numeric datetime?

Vortex — Tue, 15 Jul 2014 14:48:10 GMT

Hi Thank you. I tried your's but the day and the month got switched.

Re: How to convert character datetime to numeric datetime?

Reeza — Tue, 15 Jul 2014 14:48:49 GMT

Please mark it as answered, for future users.

Re: How to convert character datetime to numeric datetime?

Peter_C — Tue, 15 Jul 2014 15:01:09 GMT

Vortex

did you try the findfdt19. Informat?

Data direct;

input timestamp findfdt19. ;

put timestamp= datetime. ;

Cards;

27-05-2014 14:30:56

;

Re: How to convert character datetime to numeric datetime?

vikrant — Sat, 09 Jan 2016 11:58:14 GMT

Hi,

I am using your code too. I have to read 2 dates and they are of the form 2015-10-01.

The first column works perfectly .However, the second column does not work and I am using the same code for both. Please help

data C;
input text_dt $19. text_dte $20.;
datalines;
2015-10-06 09:53:25 2015-10-07 07:51:01
;
run;

data C;
format actual_start actual_end datetime20.;
set C;
actual_start =dhms(input(scan(text_dt,1,' '),yymmdd10.),scan(scan(text_dt,2,' '),1,':'),
scan(scan(text_dt,2,' '),2,':'),scan(scan(text_dt,2,' '),3,':'));

actual_end =dhms(input(scan(text_dte,1,' '),yymmdd10.),scan(scan(text_dte,2,' '),1,':'),
scan(scan(text_dte,2,' '),2,':'),scan(scan(text_dte,2,' '),3,':'));
run;

ERROR :-

Invalid argument to function INPUT at line 71 column 20.

actual_start=06OCT2015:09:53:25 actual_end=. text_dt=2015-10-06 09:53:25 text_dte=2015-10-07 07:51:01 _ERROR_=1 _N_=1

Re: How to convert character datetime to numeric datetime?

vikrant — Sat, 09 Jan 2016 12:49:32 GMT

Hi,

Sorry for the confusion. I fixed this using colum delimiters as follows:-

data input_data;
input text_dt $ 1-19 text_dte $ 21-40;

Rest of the code is same.

Thanks

Re: How to convert character datetime to numeric datetime?

data_null_step — Thu, 28 Apr 2016 20:04:35 GMT

A less complex function also works. For future reference.

DATA XX(DROP = DATE DATEVARIABLE);

SET DSN;

DATE = SUBSTR(DATEVARIABLE,1,10);

NEW_DT = INPUT(DATE, ANYDTDTE11.);

FORMAT NEW_DT MMDDYY8.;

RUN;

Re: How to convert character datetime to numeric datetime?

jstrode2010 — Mon, 11 Jul 2016 20:16:00 GMT

"Solution" in this thread does not work.

Re: How to convert character datetime to numeric datetime?

TravisY — Thu, 06 Oct 2016 22:17:45 GMT

Here's a neater solution.

num_datetime=input(text_datetime,anydtdtm23.);
Format num_datetime DateTime23.3;

Re: How to convert character datetime to numeric datetime?

rajeshV89 — Fri, 16 Mar 2018 15:13:09 GMT

why did u used anydtdtm23. here?

Re: How to convert character datetime to numeric datetime?

rajeshV89 — Fri, 16 Mar 2018 16:58:08 GMT

how to get a datepart and timepart from anydtdtm. data without using character functions...

data mh17;
format edate datetime19.;
input edate anydtdtm.;
datalines;
12:01:2010:11:33:45
;
run;