Datalines with DATETIME variable

[salvavit]

Hi all. I am trying to insert an ID and its corresponding Datetime to a table using Datalines (or cards) function. 

I have been trying with the following:

data MY_LIST;  
input ID DATE;
	cards;
3333333334 02JAN1930:00:00:00
	;
run;

But only the ID gets populated. Why does this happen?  Thanks for your help

[PaigeMiller]

You have to tell SAS to read a datetime value, otherwise it thinks it is a numeric and then SAS can't interpret 02JAN1930 as a numeric.

To tell SAS you have a datetime value, you need to use the DATETIME informat in the input statement.

data MY_LIST;  
input ID DATE:datetime18.;
format date datetime18.;
	cards;
3333333334 02JAN1930:00:00:00
	;
run; 

[ballardw · Accepted solution]

You have to tell SAS to read that value as a datetime with an appropriate informat:

data MY_LIST;  
   input ID DATE :datetime18.;
   format date datetime20.;
cards;
3333333334 02JAN1930:00:00:00
	;
run;

Then assign a format so it looks like a datetime value to people.

If you do not tell SAS a value is something other than a plain number then the Input attempts to read it as a number. Your LOG should have shown an invalid data message.

Always check the Log. And when something goes unexpected read if very carefully.

Your code would show something like:

1458  data MY_LIST;
1459     input ID DATE ;
1460  cards;

NOTE: Invalid data for DATE in line 1461 12-29.
RULE:       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+-
1461        3333333334 02JAN1930:00:00:00
ID=3333333334 DATE=. _ERROR_=1 _N_=1
NOTE: The data set WORK.MY_LIST has 1 observations and 2 variables.

Invalid data messages mean that the value encountered when reading the data did not match the expected type set by the Input statement or other method (Informat, Attribute statements).

The colon before the datetime18. informat is to address possibly differences in lengths of the values encountered in list input.

It is generally a good idea to use Identifier variables as character. If you don't do arithmetic with it character is safer. Long identifiers, 16 characters or more, often do not fit into numeric values due to precision of storage. So you might be better off to use

data MY_LIST;  
   input ID :$12. DATE :datetime18.;
   format date datetime20.;
cards;
3333333334 02JAN1930:00:00:00
	;
run;

to allow up to 12 characters for the ID value.

[salvavit]

Hello ballardw and thanks for your answer and kind explanation.
I tried your code (the second one) and it actually works. Yet I still
don't get what's the difference between "input ID $12." and "input ID
:$12.". I tried running the former and it did not work, while the
latter led to such an elegant solution 🙂

[ballardw]

---

@salvavit wrote:
Hello ballardw and thanks for your answer and kind explanation.
I tried your code (the second one) and it actually works. Yet I still
don't get what's the difference between "input ID $12." and "input ID
:$12.". I tried running the former and it did not work, while the
latter led to such an elegant solution 🙂

---

Didn't work is very vague.

The issue is partially to related to LONG ago history of reading actual punch cards.

When you use an informat on an input statement like Input x $12. the program reads all 12 characters regardless. So if the length, the 12 in this case, is longer than the value it reads what follows. Which will often leave the read pointer in the middle of next value when the following variable is read.

Your Id likely actually had the first digit of the datetime value. The colon modifies this behavior to stop at the delimiter, which is a space in simple list input for this type of data.

You can instead of the colon modifier use an INFORMAT or ATTRIBUTE statement to set the informat to read the data such as:

data MY_LIST;  
   informat ID $12. DATE datetime18.;
   input ID DATE;
   format date datetime20.;
cards;
3333333334 02JAN1930:00:00:00
	;
run;