SAS won't let me create a datetime column. Log says I'm misspelling date

[Datino]

Using Enterprise Guide.

When I run the attached code, I get the following (relevant snips) in the log:

24 create table test (valid_from datetime format=datetime20.);
________
1
WARNING 1-322: Assuming the symbol DATE was misspelled as datetime.

create table WORK.TEST( bufsize=8192 )
(
valid_from num format=DATETIME20. informat=DATE.
);

30 quit;

How do I create datetime columns?

proc sql;
	create table test (valid_from datetime format=datetime20.);
quit;


proc sql;
 describe table test;
quit;

[collinelliot · Accepted solution]

Just specify "num" instead of datetime. Datetime values are just numeric fields, so as long as you're populating the tables with valid SAS datetime values you should be fine.

[collinelliot]

e.g. 

proc sql;
	create table test (valid_from num format=datetime20.);
quit;

proc sql;
 describe table test;

 insert into test
 values('01JUN2015:00:12:00'dt);
quit;

[Datino]

Thank you, I thought the logical thing would be to declare the type like one does with date columns, I was wrong.

[Reeza]

Dates are numbers too, not a type either.

[Reeza]

There is no datetime type in SAS, only numbers and characters. Change it to NUM and it works like a charm. 

Datetime is the number of seconds from Jan 1, 1960.

proc sql;
	create table test (valid_from num format=datetime20.);
quit;


proc sql;
 describe table test;
quit;

[Tom]

A datetime value is stored in a numeric variable. Just use any of the aliases that PROC SQL accepts for a number.

Syntax error, expecting one of the following: 
CHAR, CHARACTER, DATE, DEC, DECIMAL, FLOAT, INT
, INTEGER, NUM, NUMERIC, REAL, SMALLINT, VARCHAR.

So anything except CHAR, CHARACTER or VARCHAR will work.

proc sql;
  create table test (valid_from real format=datetime20.);
  describe table test;
quit;

Or you could just create the table using a data step, no need to bother with SQL.

data test ;
  attrib valid_from format=datetime20.;
  stop;
run;
proc contents data=test;
run;