topic Re: Macro statment in Proc SQL in SAS Programming

Macro statment in Proc SQL

SAS_inquisitive — Fri, 06 May 2016 19:21:45 GMT

%macro m(text);
  proc sql noprint;
   create table t as
     select *
	   from sashelp.class
	 %if &text ^=%str() %then where name="William"; %str(;)
   quit;
%mend m;
%m(tx)

I have a SAS program above. When I remove %str( ; ), it does not work.

Re: Macro statment in Proc SQL

FreelanceReinh — Fri, 06 May 2016 19:26:48 GMT

Hello @SAS_inquisitive,

I think you can simplify the line "%if ..." to

%if &text ^= %then where name="William";;

The first semicolon ends the %THEN statement, the second semicolon ends the SELECT statement.

Re: Macro statment in Proc SQL

ballardw — Fri, 06 May 2016 19:31:41 GMT

You should specify "how it doesn't work".

If you run the not working code with Options mprint;

I think you will see that there is no semi-colon in the code before the Quit. So Quit is encountered in an expected place:

...

from sashelp.class

quit;

...

from sashelp.class

where name="William"

quit;

Re: Macro statment in Proc SQL

SAS_inquisitive — Fri, 06 May 2016 19:38:56 GMT

@FreelanceReinhand @ballardw. Thank you. I also wonder the progam flow of this code. Does the %IF statment executes first to generate the SAS statements before Proc Sql statement?

Re: Macro statment in Proc SQL

FreelanceReinh — Fri, 06 May 2016 19:49:59 GMT

@SAS_inquisitive wrote:

Does the %IF statment executes first to generate the SAS statements before Proc Sql statement?

Yes, sure. That's how SAS macro language works. Roughly speaking, the macro processor generates the code (data steps, proc steps, other non-macro statements) that is eventually sent to the compiler. Please see the documentation How SAS Processes Statements with Macro Activity for a detailed overview.