topic %include in PROC SQL? in SAS Procedures

%include in PROC SQL?

TedShelly — Wed, 19 Oct 2011 01:26:21 GMT

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?

%include in PROC SQL?

Doc_Duke — Wed, 19 Oct 2011 02:01:56 GMT

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

Re: %include in PROC SQL?

MikeZdeb — Wed, 19 Oct 2011 02:12:50 GMT

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;

%include in PROC SQL?

Ksharp — Wed, 19 Oct 2011 02:42:43 GMT

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

%include in PROC SQL?

TedShelly — Wed, 19 Oct 2011 12:08:18 GMT

Thanks. I think this will work for me.

%include in PROC SQL?

art297 — Wed, 19 Oct 2011 12:28:22 GMT

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;

%include in PROC SQL?

Howles — Wed, 19 Oct 2011 12:34:56 GMT

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?

Re: %include in PROC SQL?

MikeZdeb — Wed, 19 Oct 2011 14:43:46 GMT

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