Help using Base SAS procedures

%include in PROC SQL?

Reply
Occasional Contributor
Posts: 10

%include in PROC SQL?

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?

Trusted Advisor
Posts: 2,115

%include in PROC SQL?

Posted in reply to TedShelly

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

Valued Guide
Posts: 765

Re: %include in PROC SQL?

Posted in reply to TedShelly

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;


Occasional Contributor
Posts: 10

%include in PROC SQL?

Thanks.  I think this will work for me.

Super User
Posts: 10,018

%include in PROC SQL?

Posted in reply to TedShelly

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

PROC Star
Posts: 7,467

%include in PROC SQL?

Posted in reply to TedShelly

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;

Regular Contributor
Posts: 184

%include in PROC SQL?

Posted in reply to TedShelly

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?

Valued Guide
Posts: 765

Re: %include in PROC SQL?

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

Ask a Question
Discussion stats
  • 7 replies
  • 1606 views
  • 3 likes
  • 6 in conversation