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

[fengyuwuzu]

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

[smijosimon]

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 .

[fengyuwuzu]

to read

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

[smijosimon]

data test;

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

	smj = input(date1 ,ANYDTDTM.);

run;

This worked for me !

[fengyuwuzu]

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;

[ballardw]

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.

[fengyuwuzu]

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

[smijosimon]

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 

[fengyuwuzu]

I printed out the first 10 rows of the data:

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

[fengyuwuzu]

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

[fengyuwuzu]

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.

[andreas_lds · Accepted solution]

---

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;

[fengyuwuzu]

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

[ballardw]

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

[smijosimon]

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;