DATA Step, Macro, Functions and more

converting varchar(26) to datetime

Reply
Contributor
Posts: 35

converting varchar(26) to datetime

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 

 

Super User
Posts: 5,257

Re: converting varchar(26) to datetime

[ Edited ]

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

Data never sleeps
Contributor
Posts: 30

Re: converting varchar(26) to datetime

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

Super User
Super User
Posts: 7,406

Re: converting varchar(26) to datetime

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;
Contributor
Posts: 35

Re: converting varchar(26) to datetime

nothing is working 

Super User
Super User
Posts: 7,406

Re: converting varchar(26) to datetime

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. 

Contributor
Posts: 30

Re: converting varchar(26) to datetime

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

 

Super User
Posts: 6,946

Re: converting varchar(26) to datetime


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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,508

Re: converting varchar(26) to datetime

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;
Ask a Question
Discussion stats
  • 8 replies
  • 142 views
  • 0 likes
  • 6 in conversation