I used informat anydtdt40. and format datetime. to read them in, but they are not read in at all. all blank.
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;
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 .
to read
anydtdte. does not work either. all values are blank after importing
data test; date1 = '2014-01-01-10.11.47.518000'; smj = input(date1 ,ANYDTDTM.); run;
This worked for me !
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;
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.
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
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
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
if I read them as characters, I got this (a screen cut of first 5 lines):
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.
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.