topic Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000 in SAS Programming

what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Thu, 18 Feb 2016 20:10:47 GMT

I used informat anydtdt40. and format datetime. to read them in, but they are not read in at all. all blank.

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

smijosimon — Thu, 18 Feb 2016 20:31:55 GMT

is it to read or write in that format ?

to read u can use anydtdte. informat

to write in that format you will need to use picture in proc format .

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Thu, 18 Feb 2016 20:35:58 GMT

to read

anydtdte. does not work either. all values are blank after importing

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

smijosimon — Thu, 18 Feb 2016 20:54:57 GMT

data test;

	date1 = '2014-01-01-10.11.47.518000';

	smj = input(date1 ,ANYDTDTM.);

run;

This worked for me !

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Thu, 18 Feb 2016 21:08:04 GMT

Thanks. Right, your code works.

I use the following infile code, which does not work. Not sure where the problem is.

   data WORK.LOGIN ;
    infile 'D:\data\LOGIN.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat MEMBER_ID $34. ;
       informat LOGIN_TIMESTAMP ANYDTDTM. ;
       informat LOGOUT_TIMESTAMP ANYDTDTM. ;
       format MEMBER_ID $34. ;
       format LOGIN_TIMESTAMP datetime. ;
       format LOGOUT_TIMESTAMP datetime. ;
    input
                MEMBER_ID $
                LOGIN_TIMESTAMP 
                LOGOUT_TIMESTAMP;
    run;

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

ballardw — Fri, 19 Feb 2016 04:36:47 GMT

Were there any messages in the log?

Also did you try explicitly setting a width for reading such as:

informat LOGIN_TIMESTAMP ANYDTDTM26. ;

If that doesn't help you should post any messages from the log and a few lines of example data that shows the behavior.

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

smijosimon — Fri, 19 Feb 2016 14:26:47 GMT

show us a sample of ur data

additionally if u can try using all 3 vars as character nad then do a proc print to see what is the data being passed on the variables to ensure data is not truncated

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Fri, 19 Feb 2016 14:33:57 GMT

I tried anydtdtm40. and anydtdtm26, did not work.

LOGIN_TIMESTAMP	LOGOUT_TIMESTAMP
2014-01-01-10.11.47.518000	2014-01-01-11.12.11.594000
2014-01-01-10.46.56.362000	2014-01-01-10.50.33.830000
2014-01-01-13.00.56.340000	2014-01-01-13.19.28.438000
2014-01-01-13.04.51.569000	2014-01-01-13.24.16.206000
2014-01-01-13.10.43.651000	2014-01-01-13.29.27.735000
2014-01-01-20.36.43.038000	2014-01-01-21.23.46.843000
2014-01-01-19.31.08.188000	2014-01-01-20.06.23.152000
2014-01-01-20.41.12.057000	2014-01-01-21.36.21.783000
2014-01-01-10.13.22.648000	2014-01-01-10.55.23.402000
2014-01-01-12.07.37.904000	2014-01-01-12.33.14.021000
2014-01-01-15.08.29.764000	2014-01-01-15.47.05.939000
2014-01-01-20.59.14.579000	2014-01-01-21.01.09.404000
2014-01-01-19.22.55.049000	2014-01-01-19.48.15.591000

above is the part of the data, the two columns I have problem.

Below is from the log file:

NOTE: The infile 'D:\data\LOGIN.csv' is:

      Filename=D:\data\LOGIN.csv,
      RECFM=V,LRECL=32767,
      File Size (bytes)=129790621,
      Last Modified=20Nov2034:08:54:54,
      Create Time=09Dec2015:12:16:28

NOTE: 1410427 records were read from the infile 'D:\data\LOGIN.csv'.
      The minimum record length was 64.
      The maximum record length was 92.
NOTE: The data set WORK.LOGIN has 1410427 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           1.37 seconds
      cpu time            0.99 seconds

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Fri, 19 Feb 2016 14:52:12 GMT

I printed out the first 10 rows of the data&colon;

54   data _NULL_;
55   infile "D:\data\LOGIN.csv" obs=10;
56   input;
57   put _infile_;
58   run;

NOTE: The infile "D:\data\LOGIN.csv" is:
      Filename=D:\data\LOGIN.csv,
      RECFM=V,LRECL=32767,
      File Size (bytes)=129790621,
      Last Modified=20Nov2034:08:54:54,
      Create Time=09Dec2015:12:16:28

MEMBER_ID,LOGIN_TIMESTAMP,LOGOUT_TIMESTAMP
"101819860625","2014-01-01-10.11.47.518000","2014-01-01-11.12.11.594000"
"111619621641","2014-01-01-10.46.56.362000","2014-01-01-10.50.33.830000"
"100319558420","2014-01-01-13.00.56.340000","2014-01-01-13.19.28.438000"
"100319558420","2014-01-01-13.04.51.569000","2014-01-01-13.24.16.206000"
"100319558420","2014-01-01-13.10.43.651000","2014-01-01-13.29.27.735000"
"012719344260","2014-01-01-20.36.43.038000","2014-01-01-21.23.46.843000"
"110119839130","2014-01-01-19.31.08.188000","2014-01-01-20.06.23.152000"
"110119839130","2014-01-01-20.41.12.057000","2014-01-01-21.36.21.783000"
"062119809305","2014-01-01-10.13.22.648000","2014-01-01-10.55.23.402000"
NOTE: 10 records were read from the infile "D:\data\LOGIN.csv".
      The minimum record length was 49.
      The maximum record length was 92.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Fri, 19 Feb 2016 14:58:33 GMT

if I read them as characters, I got this (a screen cut of first 5 lines):

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Fri, 19 Feb 2016 15:07:01 GMT

right now, the solution I can find is, first read the time in as characters, and then use the following

data new;
set LOGIN;
new_login = input(LOGIN_TIMESTAMP, anydtdtm.);
format new_login datetime.;
run;

to convert to datetime format. It works. Not sure the reason why I cannot import as datetime format directly.

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

andreas_lds — Fri, 19 Feb 2016 15:47:50 GMT

fengyuwuzu schrieb:
[...]

to convert to datetime format. It works. Not sure the reason why I cannot import as datetime format directly.

Imho the quotes prevent importing the dates directly.

Seems to work:

proc fcmp outlib=work.functions.date;
   function transform(s $);
      return (input(dequote(s), anydtdtm.));
   endsub;
run;

options cmplib=(work.functions);

proc format;
   invalue importDatetime 
      other=[transform()]
   ;
run;


data work.have;
   length MEMBER_ID $ 30 LOGIN_TIMESTAMP LOGOUT_TIMESTAMP 8;

   informat LOGIN_TIMESTAMP LOGOUT_TIMESTAMP importDatetime.;

   infile datalines4 delimiter="," dsd;
   input MEMBER_ID LOGIN_TIMESTAMP LOGOUT_TIMESTAMP;   

   datalines4;
"101819860625","2014-01-01-10.11.47.518000","2014-01-01-11.12.11.594000"
"111619621641","2014-01-01-10.46.56.362000","2014-01-01-10.50.33.830000"
"100319558420","2014-01-01-13.00.56.340000","2014-01-01-13.19.28.438000"
"100319558420","2014-01-01-13.04.51.569000","2014-01-01-13.24.16.206000"
"100319558420","2014-01-01-13.10.43.651000","2014-01-01-13.29.27.735000"
"012719344260","2014-01-01-20.36.43.038000","2014-01-01-21.23.46.843000"
"110119839130","2014-01-01-19.31.08.188000","2014-01-01-20.06.23.152000"
"110119839130","2014-01-01-20.41.12.057000","2014-01-01-21.36.21.783000"
"062119809305","2014-01-01-10.13.22.648000","2014-01-01-10.55.23.402000"
;;;;
run;

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

smijosimon — Fri, 19 Feb 2016 15:42:49 GMT

Can you try this code -

data WORK.LOGIN ;
        infile 'D:\data\LOGIN.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

       format MEMBER_ID $34. ;
       format LOGIN_TIMESTAMP datetime. ;
       format LOGOUT_TIMESTAMP datetime. ;

    input
                MEMBER_ID          $CHAR12.
                LOGIN_TIMESTAMP    ANYDTDTM.
                LOGOUT_TIMESTAMP   ANYDTDTM.  ;
run;

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Fri, 19 Feb 2016 16:02:45 GMT

Thank you, smijosimon. The same thing, still blank.

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

fengyuwuzu — Fri, 19 Feb 2016 16:03:51 GMT

Yes, andreas_lds! This solved the problem. Now I have learned this which will be helpful in future.

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

ballardw — Fri, 19 Feb 2016 23:23:13 GMT

OP may not read this but there actually is a data character issue.

The MONTH valueof the Logout timestamp is not a simple value. When I paste this

2014-01-01-11.12.11.594000

Into my SAS editor the fir 01, appearing in black above was hightlighted by the editor settings as non-numeric. And when I was using the cursor keys to count columns I had to press a key TWICE to go from the - to the 0 at that location.

This makes suspect a partial problem with the encodeing.

And when I changed the LOGIN_Timestamp to look like

2014-01-01-10:11:47.518000

then that reads fine. Which concurs with my 9.2 documentation that says : are time delimiters for hours and minutes and . are date delimiters. Since you had, in effect, a date value (2014-01-01) followed by a date value (10.11.47 which would be 17 Oct 1947) you get something the informat isn't instructed to handle and results in missing

Re: what time format should I use for values like this: 2014-01-01-10.11.47.518000

Tom — Fri, 19 Feb 2016 23:46:52 GMT

Since you are using periods instead of colons before the hours, minutes and seconrd the extra period before the fractional seconds is confusing it. You can get the INPUT() or INPUTN() function to convert the string if you ignore the fractional seconds. So if you use ANYDTDTM19. informat then it works, but it does not read the '.51800' part. Also note that when you use DSD option on the INFILE statement the length specified in the INFORMAT is ignored.