topic Re: Reading a DATETIME format variable into SAS in SAS Procedures

Reading a DATETIME format variable into SAS

braveblade — Mon, 18 Jul 2016 23:27:51 GMT

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!!!

Re: Reading a DATETIME format variable into SAS

LaurieF — Mon, 18 Jul 2016 23:54:19 GMT

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;

Re: Reading a DATETIME format variable into SAS

ballardw — Tue, 19 Jul 2016 16:34:39 GMT

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.

Re: Reading a DATETIME format variable into SAS

Tom — Tue, 19 Jul 2016 16:54:04 GMT

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
;

Re: Reading a DATETIME format variable into SAS

braveblade — Tue, 19 Jul 2016 16:54:28 GMT

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;

Re: Reading a DATETIME format variable into SAS

Tom — Tue, 19 Jul 2016 16:55:58 GMT

When using the DSD option SAS will ignore the width specified in the INFORMAT and just use the width of the actual data.

Re: Reading a DATETIME format variable into SAS

braveblade — Tue, 19 Jul 2016 16:59:00 GMT

oh that's why. You guys are wonderful!!! Thanks so much!

Re: Reading a DATETIME format variable into SAS

LaurieF — Tue, 19 Jul 2016 22:26:04 GMT

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.