BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fengyuwuzu
Pyrite | Level 9

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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

16 REPLIES 16
smijosimon
Calcite | Level 5

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
Pyrite | Level 9

to read

 

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

smijosimon
Calcite | Level 5
data test;

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

	smj = input(date1 ,ANYDTDTM.);

run;

This worked for me !

fengyuwuzu
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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
Calcite | Level 5

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
Pyrite | Level 9

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
Pyrite | Level 9

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

 

 

Capture.PNG

fengyuwuzu
Pyrite | Level 9

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
Jade | Level 19

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
Pyrite | Level 9
Yes, andreas_lds! This solved the problem. Now I have learned this which will be helpful in future.
ballardw
Super User

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
Calcite | Level 5

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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