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

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

Datino — Tue, 07 Mar 2017 21:55:50 GMT

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;

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

collinelliot — Tue, 07 Mar 2017 22:02:52 GMT

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.

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

Reeza — Tue, 07 Mar 2017 22:03:29 GMT

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;

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

collinelliot — Tue, 07 Mar 2017 22:04:14 GMT

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;

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

Tom — Tue, 07 Mar 2017 22:05:55 GMT

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;

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

Datino — Tue, 07 Mar 2017 22:14:49 GMT

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

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

Reeza — Tue, 07 Mar 2017 22:16:43 GMT

Dates are numbers too, not a type either.