topic Re: converting varchar(26) to datetime in SAS Programming

converting varchar(26) to datetime

Rohit12 — Thu, 01 Jun 2017 11:41:53 GMT

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

Re: converting varchar(26) to datetime

LinusH — Thu, 01 Jun 2017 12:06:18 GMT

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

Re: converting varchar(26) to datetime

ErikLund_Jensen — Thu, 01 Jun 2017 12:09:05 GMT

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

Re: converting varchar(26) to datetime

RW9 — Thu, 01 Jun 2017 12:09:07 GMT

A full list of informats can be foudn here:

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

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;

Re: converting varchar(26) to datetime

Rohit12 — Thu, 01 Jun 2017 12:15:59 GMT

nothing is working

Re: converting varchar(26) to datetime

ErikLund_Jensen — Thu, 01 Jun 2017 12:16:09 GMT

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

Re: converting varchar(26) to datetime

RW9 — Thu, 01 Jun 2017 12:28:51 GMT

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.

Re: converting varchar(26) to datetime

Kurt_Bremser — Thu, 01 Jun 2017 12:48:01 GMT

@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;

Re: converting varchar(26) to datetime

ballardw — Thu, 01 Jun 2017 14:50:22 GMT

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;