DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

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

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

 

 


Accepted Solutions
Solution
‎02-19-2016 11:02 AM
Super Contributor
Posts: 345

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

[ Edited ]
Posted in reply to fengyuwuzu

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;

View solution in original post


All Replies
Occasional Contributor
Posts: 8

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

Posted in reply to fengyuwuzu

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 .

Super Contributor
Posts: 318

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

Posted in reply to smijosimon

to read

 

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

Occasional Contributor
Posts: 8

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

Posted in reply to fengyuwuzu
data test;

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

	smj = input(date1 ,ANYDTDTM.);

run;

This worked for me !

Super Contributor
Posts: 318

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

Posted in reply to smijosimon

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;
Super User
Posts: 11,343

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

Posted in reply to fengyuwuzu

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.

Super Contributor
Posts: 318

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

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

Occasional Contributor
Posts: 8

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

Posted in reply to fengyuwuzu

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 

Super Contributor
Posts: 318

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

Posted in reply to smijosimon

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

Super Contributor
Posts: 318

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

[ Edited ]
Posted in reply to smijosimon

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

 

 

Capture.PNG

Super Contributor
Posts: 318

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

Posted in reply to 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.

Solution
‎02-19-2016 11:02 AM
Super Contributor
Posts: 345

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

[ Edited ]
Posted in reply to fengyuwuzu

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

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

Posted in reply to andreas_lds
Yes, andreas_lds! This solved the problem. Now I have learned this which will be helpful in future.
Super User
Posts: 11,343

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

Posted in reply to fengyuwuzu

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

Occasional Contributor
Posts: 8

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

Posted in reply to fengyuwuzu

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 333 views
  • 0 likes
  • 5 in conversation