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.
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)
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)
Thank you very much Quentin.....the double quote did the trick.
Roberto-
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: