Cannot insert today() via PROC SQL ?

[bentleyj1]

I have a SAS data set with two variables:  load_dt, a date value formatted as mmddyy10, and n_recs_sas, a numeric value.

I want to use PROC SQL to insert a new record.  load_dt must have the current date.  I'm loading n_recs_sas from a macro variable.

The query is not letting me use the today() or date() function in the VALUES clause.  The error message is explicit, but can anyone tell me why?  I know there are a couple work arounds but I'd like to understand what's going on here.

714 proc sql;

715 insert into test2

716 (load_dt, n_recs_sas)

717 values (

718 today() as load_dt format=mmddyy10.

-----

22

76

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,

a missing value, +, -, MISSING, NULL, USER.

ERROR 76-322: Syntax error, statement will be ignored.

719 , input(put("&_numRecsInFinalDSN",$8.),8.)

720 );

721 quit;

NOTE: The SAS System stopped processing this step because of errors.

[Tom · Accepted solution]

VALUES wants constants, not functions or queries. You can use %SYSFUNC() to call TODAY() function and have result appear as if you typed it.  The same actually applies to your macro variable.  So your statement becomes:

  insert into test2 (load_dt , n_recs_sas)

  values (%sysfunc(today()),&_numRecsInFinalDSN)

  ;

[KarlK]

Another alternative is to use the "set" syntax of the insert statement:

proc sql;

insert into test2 set load_dt = today();

quit;

In addition, I don't think there are any circumstances in which the insert statement allows "as" or "format=" as you have in your OP.