DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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

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;

Accepted Solutions
Solution
‎03-07-2017 05:14 PM
PROC Star
Posts: 288

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

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.

View solution in original post


All Replies
Solution
‎03-07-2017 05:14 PM
PROC Star
Posts: 288

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

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.

PROC Star
Posts: 288

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

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;
Occasional Contributor
Posts: 18

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

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

Super User
Posts: 17,840

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

Dates are numbers too, not a type either.

Super User
Posts: 17,840

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

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;
Super User
Super User
Posts: 6,502

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

[ Edited ]

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;

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 165 views
  • 1 like
  • 4 in conversation