DATA Step, Macro, Functions and more

SQL Pass Through in macro

Reply
Contributor
Posts: 67

SQL Pass Through in macro

[ Edited ]

Hello,

 

 

What might be the reason  sas do not the run Macro with Pass Throught while the same statment out of macro is working properly.

 

for instance:

 

 

%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    
last_day(add_months(to_date(&sql_x_date),%eval(-&i)))-&a
and last_day(add_months(to_date(&sql_x_date),%eval(-&i+1))) disconnect from oracle; quit; %proc19_1; %end; %mend;

 

Thank you.

 

Regular Contributor
Posts: 204

Re: SQL Pass Through in macro

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.

 

Contributor
Posts: 67

Re: SQL Pass Through in macro

Posted in reply to error_prone
I ve called macro GEN, however did`t post it here, even if I exclude macro proc19_1 is the same, after calling macro nothing happend even log stay empty, I am working on EG.
Super User
Super User
Posts: 7,942

Re: SQL Pass Through in macro

[ Edited ]

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;

 

Super User
Posts: 9,925

Re: SQL Pass Through in macro

I see two %macro definitions, but only one corresponding %mend.

 

Please post complete code and log.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: SQL Pass Through in macro

Posted in reply to KurtBremser

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.

 

 

Super User
Posts: 9,925

Re: SQL Pass Through in macro

As long as you have an unfinished macro definition, no code will run, and therefore no meaningful log will be produced.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: SQL Pass Through in macro

Posted in reply to KurtBremser
There are finished macros defintion,
you ve got 2 macros trigger by the posted macro:
%macro_start;
and
%proc19_1;
Super User
Posts: 9,925

Re: SQL Pass Through in macro

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 8 replies
  • 257 views
  • 1 like
  • 4 in conversation