Hi guys. New SAS user here. I need some help regarding reading a DATETIME format variable into SAS.
The datafile's name is "test.txt". The variables are seprated by tab:
24-Jun-2012 03:39:00 79
I used DATETIME format to read the first variable with length 20 as follows:
data testing2;
infile 'C:\test.txt' DLM='09'X DSD MISSOVER;
input date DATETIME20. number2;
RUN;
It does not work. SAS read the first variable correctly, but read second variable as missing value.
If I add an +1 in the end of the first variable, it works. I don't know why.
data testing2;
infile 'C:\test.txt' DLM='09'X DSD MISSOVER;
input date DATETIME20. number2;
RUN;
If I use the colon modifier and keep the length as 20 it works:
data testing2;
infile 'C:\test.txt' DLM='09'X DSD MISSOVER;
input date :DATETIME20. number2;
RUN;
If I use the colon modifier and use the length as 15 it still works:
infile 'C:\test.txt' DLM='09'X DSD MISSOVER;
input date :DATETIME15. number2;
RUN;
I'm so confused. The length of the first variable is 20, so :DATETIME20. should work but it doesn't. Why adding +1 or use colon modifier (even at length 15) work?
Also, am i correct on the following statement:
Formatted input: SAS Pointer moves to the next column after reading the variable with formatted input. So if the delimiter is tab, after reading the variable with formatted input, the pointer is actually at the position of TAB. In order to read next variable, I need to use +1 to skip the TAB.
Colon modifier: SAS Pointer moves to the beginning position of next variable after reading the variable with colon modifier.
Please help! Thanks!!!
When I use delimited infile code, I predefine all my variables with an attrib statement (or informat/format/length - but attrib is tidier). By not putting the length to be read in the input statement, it follows the delimiter control; the converse (as you have (understandably) done it) puts the cursor past the delimiter.
Try this:
data _null_;
infile cards dsd dlm='09'x;
attrib date informat=datetime20. format=datetime20.;
input date
num;
put date= datetime20.
num;
stop;
cards;
24-Jun-2012 03:39:00 79
;
run;
When you put the informat on the input statement it forces the entire length of the specification to be read. DSD is also interacting with your data; see the example lines of code below. I'm using a comma delimiter so it may be seen.
The first replicates your issue as an error , the other two work.
data junk;
infile datalines dlm=',' dsd;
input date DATETIME20. number2;
format
datalines;
24-Jun-2012 03:39:00,79
run;
data junk2;
infile datalines dlm=',';
input date DATETIME20. number2;
datalines;
24-Jun-2012 03:39:00,79
run;
data junk3;
infile datalines dlm=',' dsd;
informat date datetime20.;
input date number2;
datalines;
24-Jun-2012 03:39:00,79
run;
I would also check to see if your data file by chance has 2 tabs before the number as DSD will treat that, 2 consecutive delimiters, as a missing value.
Thanks! I tried the last part of codes and it still reads the full length of date and time even I change the datetime length to 13. I'm no longer putting the informat on the input statement. Could you explain why?
data junk3;
infile datalines dlm=',' dsd;
informat date datetime13.;
input date number2;
datalines;
24-Jun-2012 03:39:00,79
run;
When using the DSD option SAS will ignore the width specified in the INFORMAT and just use the width of the actual data.
oh that's why. You guys are wonderful!!! Thanks so much!
You are having trouble because of the space embedded into the value. When you use the : modifier it will only read the date part of your datatime value because of the embedded space.
As others have said just don't include the format in the INPUT statement. Use an INFORMAT statement instead to let SAS know how to read the value for that variable.
data testing2;
infile cards dlm='|' dsd truncover;
length date number2 8;
informat date datetime20.;
format date datetime20.;
input date number2;
CARDS;
24-Jun-2012 03:39:00|79
4-Jun-2012 3:39:00|79
24Jun2012 03:39:00|79
;
Not quite - the original problem posited that the variables were separated by tabs, or '09'x. As long as that's the case, internal spaces are fine.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.