How to convert character datetime with lenth of $21. to datetime19.?

[YousefOba]

hi

i am tring to conver a charecter field popelated with (2018-09-01 00:00:00) to datetime19. format .

as i have used the below script but it give me the output of only dates without time:

data want (drop=TRANSACTION_DATE  _date );
set b;

format TRANSACTION_DATE_1  YYMMDD10.  ;
informat TRANSACTION_DATE_1  YYMMDD10. ;


_date=substr(TRANSACTION_DATE,1,18);


TRANSACTION_DATE_1=INPUT(_date,YYMMDD10.); 

run;

data want2 (drop=TRANSACTION_DATE_ );
set want;

format TRANSACTION_DATE    YYMMDD10. ;
informat TRANSACTION_DATE  YYMMDD10.;

TRANSACTION_DATE=TRANSACTION_DATE_1; 


run;

Thank You

[FreelanceReinh · Accepted solution]

Hi @YousefOba and welcome to the SAS Support Communities!

So, your dataset b contains a character variable TRANSACTION_DATE of length 21 with datetime values of the form 

yyyy-mm-dd hh:mm:ss

Actually, length 19 would be sufficient for such values. As a consequence, there will be two additional blanks (most likely) in these strings. Let's make no assumption about the position of these blanks.

Example data:

data b;
length transaction_date $21;
input transaction_date $char21.;
cards;
2018-09-01 01:00:00
 2018-09-01 02:00:00
  2018-09-01 03:00:00
 2018-09-01  04:00:00
2018-09-01  05:00:00
2018-09-01   06:00:00
;

You can use the ANYDTDTM21. informat to convert these character values into SAS datetime values and then apply the DATETIME19. format to the resulting numbers:

data want(drop=transaction_date);
set b;
tadt=input(transaction_date, anydtdtm21.);
format tadt datetime19.;
run;

[Peter_C]

have a look at paper http://support.sas.com/resources/papers/proceedings14/1744-2014.pdf

VFORMAT Lets SAS® Do the Format Searching

Works for INFORMATS too

[YousefOba]

Thank you very much for support and effort.