09-11-2017 09:21 AM - edited 09-11-2017 09:22 AM
What might be the reason sas do not the run Macro with Pass Throught while the same statment out of macro is working properly.
%Macro GEN(x_date); %let Krok=&sysmacroname; %let sql_x_d=%sysfunc(putn("&x_date"d, YYMMDD.)); %let sql_x_date=%unquote(%str(%'&sql_x_d%')); %macro_start; %do i=1 %to 12; %if &i<12 %then %do; %let a=1; %end; %else %do; %let a=0; %end; proc sql; connect to oracle (user=pass password=pass path=pass); create table proc19_1 as select * from connection to oracle (select time_stamp id_cst id_trans ofer_kg rate_kg from hd.customers where time_stampt between
and last_day(add_months(to_date(&sql_x_date),%eval(-&i+1))) disconnect from oracle; quit; %proc19_1; %end; %mend;
09-11-2017 09:32 AM
Difficult to know what is not working without seeing the log with option symbolgen, mprint and mlogic active.
You don't call the macro GEN in the posted code, but the macro proc19_1.
09-11-2017 09:36 AM
09-11-2017 09:40 AM - edited 09-11-2017 09:44 AM
What is your question? Did you get any error? Please post the log.
You do not appear to be calling your macro anywhere in the code you posted.
Your pass through SQL code is missing commas and your CREATE TABLE statement is missing a semi-colon.
You don't need all of those %EVAL() in the middle of the SQL code. I am pretty sure that Oracle can figure out how to add 1 to -5 on its own.
proc sql; connect to oracle (user=pass password=pass path=pass); create table proc19_1 as select * from connection to oracle (select time_stamp , id_cst , id_trans , ofer_kg , rate_kg from hd.customers where time_stampt between last_day(add_months(to_date(&sql_x_date),-&i-&a)) and last_day(add_months(to_date(&sql_x_date),-&i+1)) ) ; disconnect from oracle; quit;
09-11-2017 10:01 AM
09-12-2017 04:34 PM
Ok, thanks I found mistakes by running code in SQLDeveloper, however, it`s strange that when I tried run code with my mistakes in SAS, I keep received blank log.
09-13-2017 01:01 AM
As long as you have an unfinished macro definition, no code will run, and therefore no meaningful log will be produced.
09-15-2017 03:20 AM
In the code you posted, there are two (2) %macro statements that start a macro definition, but there is only one (1) %mend, so one of the macro definitions is still pending, causing no code to be run.
BTW don't do nested macro definitions. SAS keeps all macros in the global scope, so nesting them just complicates your code and makes it less maintainable, but serves no purpose (apart from confusing the programmer).