topic Cannot insert today() via PROC SQL ? in SAS Programming

Cannot insert today() via PROC SQL ?

bentleyj1 — Fri, 07 Jun 2013 18:49:06 GMT

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.

-----

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.

Re: Cannot insert today() via PROC SQL ?

Tom — Fri, 07 Jun 2013 19:10:56 GMT

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)

;

Re: Cannot insert today() via PROC SQL ?

KarlK — Fri, 07 Jun 2013 19:18:07 GMT

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.