How to convert character datetime to numeric datetime?

[Vortex]

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:56In 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:00Please let me know what I should do?THank you

[Reeza]

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.)

[sas_lak]

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;

[Peter_C]

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

[Vortex]

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

[user24feb]

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;

[Vortex · Accepted solution]

Hey thank you the one by user24feb works.

[Reeza]

Please mark it as answered, for future users.

[Peter_C]

Vortex

did you try the findfdt19. Informat?

Data direct;

input timestamp findfdt19. ;

put timestamp= datetime. ;

Cards;

27-05-2014 14:30:56

;

[jstrode2010]

"Solution" in this thread does not work.

[data_null_step]

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;

[vikrant]

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

[vikrant]

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

[TravisY]

Here's a neater solution.

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

[rajeshV89]

why did u used anydtdtm23. here?