topic Re: How to read this specific datetime and then format? in New SAS User

How to read this specific datetime and then format?

d6k5d3 — Wed, 10 Oct 2018 03:37:22 GMT

Hello everyone!

I would really appreciate the help!

I am trying to make SAS read data from an imported file (.csv) having a column GMT_Time, and it appears as (for example): 13.03.2007 00:00:00.000.

I have seen the log file. If I set getnames = yes, the cells in column is read (informat) as ddmmyy10., and formatted as ddmmyy10.! However, if I set getnames=no, the cells are read (informat) as VAR1 $23. and formatted as VAR1 $23.. Neither serves my purpose.

I want SAS to format them in a datetime format so that later I can perform date and time related calculations on SAS. I cannot figure out how to solve this?

Would you please tell me what should be my informat, format and input statements be? I would copy the code from log after PROC IMPORT.

Much thanks in advance!

Re: How to read this specific datetime and then format?

PGStats — Wed, 10 Oct 2018 04:43:29 GMT

This conversion method would work:

data _null_;
dtStr = "13.03.2007 00:00:00.000";
substr(dtStr,3,1) = "-";
substr(dtStr,6,1) = "-";
dt = input(dtStr, anydtdtm23.);
format dt datetime23.;
put _all_;
run;

it replaces the periods in the date with hyphens and uses the anydtdtm. informat.

Re: How to read this specific datetime and then format?

d6k5d3 — Wed, 10 Oct 2018 06:22:16 GMT

PG, thank you very much for your reply.

I have more than a million rows of data, and I wanted to import and format the variables by copying from the log file after PROC IMPORT. Is there a way to do the same thing just by altering some codes from the following log file:

data WORK.HUF1 ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'C:\Users\Deepan\OneDrive\xyz.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat Gmt_time informat. ;

...

format Gmt_time informat. ;

...

input Gmt_time;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;

I wholeheartedly appreciate your time.

Much thanks.

Re: How to read this specific datetime and then format?

Kurt_Bremser — Wed, 10 Oct 2018 06:36:37 GMT

Use an intermediate variable:

informat _gmt_time $23.;
format gmt_time e8601dt23.3;
input _gmt_time;
gmt_time = dhms(input(scan(_gmt_time,1),ddmmyy10.),0,0,input(scan(_gmt_time,2)time12.3));
drop _gmt_time;

Re: How to read this specific datetime and then format?

d6k5d3 — Thu, 11 Oct 2018 01:23:12 GMT

Thank you very much for the pieces of code. I do not know why I was getting error. However, using the same knowledge I later managed to get the desired outcome. My codes are the following:

Data Huf; 
set Huf1;
 DT=DHMS(Input(Scan(Gmt_time,1,''),DDMMYY10.),Scan(Scan(Gmt_time,2,''),1,':'),Scan(Scan(Gmt_time,2,''),2,':'),Scan(Scan(Gmt_time,2,''),3,':'));
  Format DT DateTime19.;
run;