DATA Step, Macro, Functions and more

Cannot insert today() via PROC SQL ?

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Cannot insert today() via PROC SQL ?


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.


Accepted Solutions
Solution
‎06-07-2013 03:10 PM
Super User
Super User
Posts: 7,060

Re: Cannot insert today() via PROC SQL ?

Posted in reply to bentleyj1

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)

  ;

View solution in original post


All Replies
Solution
‎06-07-2013 03:10 PM
Super User
Super User
Posts: 7,060

Re: Cannot insert today() via PROC SQL ?

Posted in reply to bentleyj1

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)

  ;

Contributor
Posts: 65

Re: Cannot insert today() via PROC SQL ?

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 2585 views
  • 0 likes
  • 3 in conversation