too many arguments in variable

[RobertoOttawa]

I am trying to pass a variable value which is the result of PROC SQL into another PROC SQL.

SQL_LIB ODBC DSN=EMILI_GIS_SANDBOX ;

proc sql noprint;

select max(RECREATS)

  into :maxdate

from SQL_LIB.PSAD_PROSALE;

run;

proc sql noprint;

create table PCPERM.PSAD_SALE_UPDT as

select * from  TABLE1

where RECREATS > &maxdate and RECLEFDT = '9999-12-31' and RECDELTS = '9999-12-31-24.00.00.000000';

Unfortunately I get the following error

18         proc sql noprint;

19         create table PCPERM.PSAD_SALE_UPDT as

20         select *

21         from '\\nath13\MainFrame_Data\Emili_PRAM\propsale'

22         where RECREATS > &maxdate and RECLEFDT = '9999-12-31' and RECDELTS = '9999-12-31-24.00.00.000000';

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

NOTE: Line generated by the macro variable "MAXDATE".

22          2015-01-16-00.23.55.840861

                            ___

                            22

                            76

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,

              GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,

              ~=.

Even when I try to SUBTR the MAXDATE, I get

24         data null;

25         date1 = SUBSTR(&maxdate,1,16);
                   ______
                   72
NOTE: Line generated by the macro variable "MAXDATE".
25         2015-01-16-00.23.55.840861
                           ___
                           388
                           200
26         end;
           ___
           161
ERROR 72-185: The SUBSTR function call has too many arguments.

[Quentin · Accepted solution]

If you are intending to do a character evaluation, then I think the only problem is you are missing quotes, e.g.:

where recreats > "&maxdate" and .....

or

date1=substr("&maxdate",1,16)

[RobertoOttawa]

Thank you very much Quentin.....the double quote did the trick.

Roberto-