%include in PROC SQL?

[TedShelly]

I’d like to be able to include blocs of code within PROC SQL using %include statements.  But doing so generates error messages.  Is there some option or technique to accomplish this?

[Doc_Duke]

probably.  Please share your log so we can see the problem.

[MikeZdeb]

Hi ... there maybe an easier way to do this, but you can turn the code you want to include into a macro variable ...

* some SQL statements in an external file;

data _null_;

file 'z:\stuff.txt';

put 'from sashelp.class';

put 'where sex eq "M"';

run;

* create a macro variable from the statements in the external file;

data _null_;

length stuff $1000;

infile 'z:\stuff.txt' end=done;

do until (done);

  input;

  stuff = catx(' ',stuff,_infile_);

end;

call symputx('stuff',stuff);

run;

* "include" the statements using the macro variable;

proc sql;

create table males as

select *

&stuff;

quit;

[TedShelly]

Thanks.  I think this will work for me.

[Ksharp]

First of all. %include is not a part of Macro facility. So you can put the code into SQL.

But you can make a macro to include these code and use macro to put the code into SQL.

Ksharp

[art297]

You can also do it with the old-style macros.  E.g.,

macro xinc

  create table want as

    select *

      from sashelp.class

        where sex eq "M";

%

proc sql noprint;

  xinc

quit;

[Howles]

As far as I know there is no particular limitation on using %INCLUDE in PROC SQL. The rule is that %INCLUDE has to appear at a statement boundary. Of course there are not a lot of statement boundaries in SQL code.

TedShelly wrote:
I’d like to be able to include blocs of code within PROC SQL using %include statements.  But doing so generates error messages.  Is there some option or technique to accomplish this?

[MikeZdeb]

Hi ... correct (as expected !) ...

data _null_;

file 'z:\stuff.txt';

put 'create table males as select *' /

'from sashelp.class' /

'where sex eq "M";';

run;

proc sql;

%include 'z:\stuff.txt';

quit;

26   proc sql;

27   %include 'z:\stuff.txt';

NOTE: Table WORK.MALES created, with 10 rows and 5 columns.

31   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.96 seconds

      cpu time            0.14 seconds