SAS博客列表

SAS consistently expands its support for running non-SAS code inside SAS programs. It’s been a while since SAS introduced explicit SQL pass-through facility allowing SAS users to embed native Database Management Systems (DBMS) Sequel (SQL) code into its PROC SQL and PROC DS2.

Similarly, now you can run R code within PROC IML.

SAS Viya added PROC PYTHON enabling you to embed increasingly popular Python programming language code within your SAS programs.

However, while SAS developers welcome such cross-language functionality, its implementation often leads to making SAS programs bulky and overloaded, not easily readable and difficult to follow. In the spirit of modular programming, it would have been nice just referencing those “foreign” modules without actually inserting their code into SAS programs. Somewhat like %INCLUDE statement brings into SAS program pieces of SAS code stored in files.

Limitations of %INCLUDE

The problem with the %INCLUDE for bringing non-SAS code into a SAS program stems from the fact that % INCLUDE is not a SAS macro statement as it seems to be. That’s right. Despite prominently displaying % sign of distinction in front of itself, it has nothing to do with SAS macro facility. I know, it is misleading and it is a misnomer.  Let’s call it an exception. However, the fact is that %INCLUDE is a SAS global statement, and as such it is not processed by the SAS macro processor.

Being a global statement, %INCLUDE must be placed between other SAS statements (or be the very first or last statement in your SAS program). It cannot be positioned within and be part of other SAS statements.

In case of native SQL, its code resides within a SAS statement, namely inside parentheses of the FROM CONNECTION TO dbase ( ) clause of the explicit SQL pass-through construct. Therefore, placing %INCLUDE where the native SQL code is expected will generate a syntax error. Bummer!

Be %INCLUDE a true macro object the described above problem would not exist.

%EMBED macro function

Well, let’s leave the %INCLUDE alone and instead create a macro function (we'll call it %EMBED) that will do what %INCLUDE does - bringing the contents of an external code file into a SAS program.  The key difference though will be that we will make it a true macro function, which runs by the SAS macro processor during SAS program compilation (before execution). Then by the SAS program execution time, the contents of the external code file will be already injected where this macro function is called. Therefore, one can invoke (place) it inside other SAS statements.

%EMBED macro function does not care whether it embeds SAS code or non-SAS code; the contents of the external file determine what’s embedded. It is up to you, SAS developer, to place this macro call strategically where code from the external file is appropriate.

Here is how we can easily implement such a macro function:

/* -------------------------------------------------------------------
| DESCRIPTION | Macro to embed any code from a file into SAS program.
|---------------------------------------------------------------------
| INVOCATION  | %embed(full-file-name) or %embed(file-reference)
|---------------------------------------------------------------------
| AUTHOR      | Leonid Batkhan, May 2023
|-------------------------------------------------------------------*/
%macro embed(f);
  %local p ref rc fid;
  %let p = %sysfunc(findc(&f,/\:.));
  %if &p %then %let rc = %sysfunc(filename(ref, &f));
         %else %let ref = &f;
  %let fid = %sysfunc(fopen(&ref));
  %if &fid>0 %then
    %do %while(%sysfunc(fread(&fid))=0);
      %let rc = %sysfunc(fget(&fid, line, 32767));
      &line
    %end;
    %else %put ERROR: Macro &sysmacroname - file "&f" cannot be opened.;
  %let rc = %sysfunc(fclose(&fid));
  %if &p %then %let rc = %sysfunc(filename(ref));
%mend embed;

%EMBED macro function has a single argument (parameter) representing either a full-path file name (including extension) or a fileref assigned with a FILENAME statement or function. It is “smart enough” to distinguish between the file name and fileref and process them accordingly.

It returns as its value the contents of the external file specified as its argument (parameter). This value may contain multiple lines of code.

%EMBED macro function code highlights

The first %let p= statement determines whether argument f represents full-path file name (p>0) or a file reference (p=0). We deduce it from the fact that file name must contain at least one of the /\:. symbols, but fileref may not contain any of them.

Then the %if-%then-%else statement creates either its own fileref using %sysfunc(filename(ref, &f)) or assigns macro variable ref to &f (if &p>0). Since we do not provide an initial value for the ref macro variable, filename function will assign a unique system-generated fileref, which starts with #LN followed by 5 digits (e.g. #LN00009). This ensures that the fileref would not interfere with other potential filesref’s assigned outside of this macro.

Then we open this file. If file opening fails (fid=0) then we output an ERROR message in the SAS log.

If the file opens successfully (fid>0) then we loop through this file using fread( ) function (while fread=0) which loads one record per iteration into the file data buffer (FDB) and fget( ) function which copies data from the FDB to a macro variable line. Note, that in %sysfunc(fget(&fid, line, 32767)) second argument (line) do not need & in front of it.

The key here is the following line of code:

This macro variable reference (not a %put &line) just "injects" the value of macro variable line into the return value of this macro function. For each iteration of the do-loop, we read a line of code from the external file and add it to the return value of the %embed macro function.

After the loop, we close the file and conditionally de-assign fileref (if we assigned it within the macro); we do not de-assign the fileref if it is assigned outside the macro in the calling program.

%EMBED macro function usage

While we developed %EMBED macro function to address limitations of %INCLUDE for inserting native SQL code into SAS pass-through clause, its usage is much broader. One can use %EMBED for embedding/including/inserting/injecting/delivering any code, SAS or non-SAS, anywhere in the SAS program. In essence, %embed is a more advanced and robust alternative to the %include.

Let’s explore several scenarios of the %embed macro function usage.

Embedding native DBMS SQL Code into SAS program

Suppose you have a native DBMS SQL query code stored in file C:\project\query1.sql. (File extension is arbitrary, and in this case just indicates what type of code we are going to embed.)

Then you can use SQL Procedure Pass-Through Facility of the SAS PROC SQL and %embed macro function as in the following example:

proc sql;
   connect to odbc (dsn="db_name");
   create table WORK.ABC as
   select * 
      from connection to odbc ( %embed(C:\project\query1.sql) );
   disconnect from odbc;
quit;

During compilation phase, SAS macro processor will replace %embed(C:\project\query1.sql) with the contents of the external file containing DBMS-specific native SQL code (instead of SAS PROC SQL code). Then during execution time, PROC SQL will pass this query code on to the DBMS for processing.  The DBMS will return to SAS the result of this query and SAS will create data table WORK.ABC.

You can also use %embed macro function for "injecting" SQL code from a file into explicit FedSQL pass-through and explicit SQL pass-through in DS2.

Embedding Python or Lua code into SAS program

In PROC PYTHON and PROC LUA, you can use their optional INFILE= clause to reference an external file that contains their statements to run within a SAS session. Alternatively, you can use %embed macro function instead. For example, if you have your Python code stored in a file C:\project\program.py then you can place your %embed(C:\project\program.py) macro function call between submit and endsubmit statements of the PROC PYTHON:

proc python;
  submit;
    %embed(C:\project\program.py)
  endsubmit;
run;

Similar for PROC LUA:

proc lua;
  submit;
    %embed(C:\project\program.lua)
  endsubmit;
run;

Embedding R code into SAS program

Here is an example of %embed usage for inserting R code into SAS using PROC IML:

proc iml;
  submit / R;
    %embed(C:\project\program.r)
  endsubmit;
run;

Embedding SAS code into SAS program

Finally, you can embed a SAS code from external file into SAS program. You can use it instead of %include statement, for example:

data TWO;
   set ONE;
run;
 
%embed(c:\project\data_three.sas)

Unlike %include global statement which can be placed only between SAS statements, %embed can be placed anywhere in a SAS program. You can use it not only within DATA or PROC steps, but also within SAS executable statements. That means %embed can bring in SAS code that can be executed conditionally. For example:

data a;
  set b;
  if x>0 then %embed(c:\project\code1.sas);
         else %embed(c:\project\code2.sas);
run;

Obviously, your embedded code must comply with the context of its surrounding.

Conclusion

As you can see, %embed macro function presents an elegant solution for embedding both “foreign” and SAS native code without cluttering the SAS program. Would you agree that %embed macro is a more robust alternative to the %include?

Questions? Thoughts? Comments?

Do you find this post useful? Do you have questions or your own tips and tricks for embedding other code into your programs? Please share with us below.

Additional resources

• How to conditionally execute SAS global statements
• SAS opens its code editor interface to Python users
• Using PROC PYTHON to augment your SAS programs
• How to run R code from within SAS Enterprise Guide

Embedding any code anywhere into SAS programs was published on SAS Users.