SAS Proc SQL dateadd function

1
votes

I am trying to use some SQL code in SAS within a proc SQL. The original code in SQL Server had this which worked fine.

case 
when entry_stamp between '2011-10-29 21:00:00.000' and '2011-11-06 02:00:00.000' 
    then dateadd(hour, 5, cast(convert (char(16), entry_stamp, 121) as datetime))

... ... else dateadd(hour, 6, cast(convert (char(16), entry_stamp, 121) as datetime)) end ,

I get a syntax error at the brackets by the 121. A bit of googling suggests that dateadd is not supported in proc SQL?

Thanks.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, , *, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, ELSE, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, WHEN, ^, ^=, |, ||, ~, ~=.

2
sqlsasdateadd
Note that SAS stores datetimes as an integer number of seconds, so adding an hour is just adding 3600 to a datetime (and there's no concern about type conversion). – Joe

2 Answers

2
votes

PROC SQL within SAS is ANSI compliant which is why you're having issues with DATEADD.

If you're not doing a pass though query (e.g. you're processing a SAS Dataset) you can use the INTNX function.

As you're trying to add hours you'd need something like:

format hours datetime20.;
hours=intnx('hour', '01FEB2010:00:00:00'dt, 1, 'same');

The result would be:

hours= 01FEB2010:01:00:00 (next hour)

0
votes
data _null_;
   /* The following statement creates expected results. */
   date1=intnx('dtday','01aug11:00:10:48'dt,1);
    /* The following two statements create unexpected results. */
   date2=intnx('dtday','01aug11'd,1);
   date3=intnx('dtday','01aug11:00:10:48'd,1);
   put 'Correct Datetime Value   ' date1= datetime19. /
   'Incorrect Datetime Value ' date2= datetime19. /
   'Incorrect Datetime Value ' date3= datetime19.;
   run;
SAS writes the following output to the log:
  Correct Datetime Value   date1=02AUG2011:00:00:00
  Incorrect Datetime Value date2=02JAN1960:00:00:00
  Incorrect Datetime Value date3=02JAN1960:00:00:00

To ensure correct results with interval functions, use date intervals with date values 
and datetime intervals with datetime values.
SAS does not return an error message if you use a date value with a datetime interval, 
but the results are incorrect.