converting varchar(26) to datetime

[Rohit12]

data a;

input y $26.;

datalines;

2017-04-19-11.06.29.370264

2017-03-13-14.57.30.837560

2017-03-13-14.57.43.389704

2017-03-13-14.57.56.050378

2017-04-19-11.06.43.894610

2017-03-23-15.02.05.599148

;

run;

 I want variable y to be converted to datetime22.3;

2017-04-19-11.06.29.370

2017-03-13-14.57.30.838

2017-03-13-14.57.43.389

how can i do that 

[LinusH]

I don't have SAS test right now, but perhaps the YYMMDDTM. informat could work?

[ErikLund_Jensen]

Hi

If you can live without the fractions of seconds, use informat anydtdtm:

data a;
input datetime anydtdtm.;
datalines;
2017-04-19-11.06.29.370264
2017-03-13-14.57.30.837560
2017-03-13-14.57.43.389704
2017-03-13-14.57.56.050378
2017-04-19-11.06.43.894610
2017-03-23-15.02.05.599148
;
run;
data _null_; set a;
put datetime datetime22.3;
run;

19APR2017:11:06:29.000
13MAR2017:14:57:30.000
13MAR2017:14:57:43.000
13MAR2017:14:57:56.000
19APR2017:11:06:43.000
23MAR2017:15:02:05.000

[RW9]

A full list of informats can be foudn here:

http://support.sas.com/documentation/cdl/en/leforinforref/69823/HTML/default/viewer.htm#n0verk17pchh...

I suspect its one of the E8601 types your dealing with here, but you would know better.  Just read the data in using the correct informat, then format as datetime, e.g:

data want;
  set a;
  new_date=input(y,e8601dt.);
  format new_date datetime22.3;
run;

[Rohit12]

nothing is working 

[RW9]

So you have tried all those informats yes?  If so, what format is your data in, I don't know what your data is.  This for instance gets the date and time, one of the other informats may get that odd bit at the, but I don't have time to try all of them;

data a;
  input y anydtdtm.;
  format y datetime22.3;
datalines;
2017-04-19-11.06.29.370264
2017-03-13-14.57.30.837560
2017-03-13-14.57.43.389704
2017-03-13-14.57.56.050378
2017-04-19-11.06.43.894610
2017-03-23-15.02.05.599148
;
run;

If its not a standard format, then you would need to create a picture format yourself, or substring that bit out and add it on yourself. 

[ErikLund_Jensen]

The following keeps the fractions too. It is not elegant, but it seems to work:

data a;
input datetime anydtdtm19. @20 fraction 7.3 ;
datetime = datetime + fraction;
datalines;
2017-04-19-11.06.29.370264
2017-03-13-14.57.30.837560
2017-03-13-14.57.43.389704
2017-03-13-14.57.56.050378
2017-04-19-11.06.43.894610
2017-03-23-15.02.05.599148
;
run;
data _null_; set a;
put datetime datetime22.3;
run;

19APR2017:11:06:29.370
13MAR2017:14:57:30.838
13MAR2017:14:57:43.390
13MAR2017:14:57:56.050
19APR2017:11:06:43.895
23MAR2017:15:02:05.599

[Kurt_Bremser]

---

@Rohit12 wrote:

data a;

input y $26.;

datalines;

2017-04-19-11.06.29.370264

2017-03-13-14.57.30.837560

2017-03-13-14.57.43.389704

2017-03-13-14.57.56.050378

2017-04-19-11.06.43.894610

2017-03-23-15.02.05.599148

;

run;

 I want variable y to be converted to datetime22.3;

2017-04-19-11.06.29.370

2017-03-13-14.57.30.838

2017-03-13-14.57.43.389

how can i do that 

---

First of all, datetime22.3 will NOT give you your intended result, as the SAS datetime formats use the standard SAS data notation (01JAN1960)

Try this:

data a;
input y $26.;
substr(y,11,1) = 'T';
substr(y,14,1) = ':';
substr(y,17,1) = ':';
x = input(y,e8601dt26.);
format x e8601dt23.3;
datalines;
2017-04-19-11.06.29.370264
2017-03-13-14.57.30.837560
2017-03-13-14.57.43.389704
2017-03-13-14.57.56.050378
2017-04-19-11.06.43.894610
2017-03-23-15.02.05.599148
;
run;

[ballardw]

Or some ugly inline substr:

data a;
input y $26.;
ydt= dhms(input(y,yymmdd10.),0,0,input(substr(y,12,8),time7.) + Input(substr(y,20),best8.));
format ydt datetime26.6;
datalines;
2017-04-19-11.06.29.370264
2017-03-13-14.57.30.837560
2017-03-13-14.57.43.389704
2017-03-13-14.57.56.050378
2017-04-19-11.06.43.894610
2017-03-23-15.02.05.599148
;
run;