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!
If %INCLUDE were a true macro object, the problem described above 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 line; %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.
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.
As you can see %EMBED macro function addresses limitations of %INCLUDE for inserting native SQL code into SAS pass-through clause. In essence, %embed is a more advanced and robust alternative to the %include. Let's explore several other code embedding scenarios.
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. For example:
proc python infile='C:\project\program.py'; run; |
Similar for PROC LUA:
proc lua infile='C:\project\program.lua'; run; |
If you define a fileref for your external file, then you can use it without quotes in the INFILE= option.
Embedding R code into SAS program
In order to run R code within SAS program you can use PROC IML with SUBMIT / R statement:
proc iml; submit / R; <here goes R code> endsubmit; run; |
The contents of the SUBMIT block are passed as a string directly to R parser without being processed by the SAS macro preprocessor. Hence, neither %INCLUDE nor %EMBED would work there as R would not know how to interpret them, or any non-R code for that matter. In order to embed R code from an external file, you can use source function in R:
proc iml; submit / R; source(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.
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.
20 Comments
Hi Leonid,
Your blog inspired me (not the first time) so let me share my version of the macro, which I renamed to %minclude() [from "macro include"].
The code is using DoSubL() and macro-variables-array. It also have "feature" of printing the source.
I'm going to add it to the BasePlus package (https://github.com/SASPAC/baseplus), of course adding information where the source of my inspiration came from!
Sorry for not formatted code.
All the best
Bart
/* test code */ filename f 'R:\testFile.txt'; filename f list; %let someGlobalMacroVariable=17; data _null_; file f; do i=1 to 3; put "data y; x = " i "; run;"; put '%macro A(); %put ' i ' ** &someGlobalMacroVariable.; %mend; %A()'; end; run; /* macro */ %macro mInclude( f /* sas fileref or a quoted path to the file */ ,source=0 ); %local i N; %let i = %sysfunc(DoSubL(%str( options nonotes nosource; data _null_; infile &f. lrecl=32767 end=END; input; call symputx(cats("______", _N_), _infile_, "L"); if END then call symputx("N", _N_, "L"); run; ))); %if 1 = %superq(source) %then %do; %put ++ mIncluded code: ++; %put ; %do i=1 %to &N.; %put %str( +) %superq(______&i); %end; %put ; %put ++++++++++++++++++++++; %end; %do i=1 %to &N.; &&______&i. %symdel ______&i /nowarn; %end; %mend mInclude; /* test */ %mInclude(f) %mInclude(f,source=1) %mInclude('R:\testFile.txt') %mInclude('R:\testFile.txt',source=1)Hi Bart,
Great to hear that my blog post inspired you, and thank you for sharing your solution. (I did format your code, so please double check it to make sure I did not introduce any errors.)
Best regards,
Leonid
Thank you for help with formatting, it looks great now! 🙂
B.
So this is like MODE=1 of Tom Hoffman's %FREAD() macro from the year 2000 that I have a copy posted at https://github.com/sasutils/macros/blob/master/fread.sas.
Tom's macro requires that you %UNQOTE() the result if you want to result to RUN instead being inserted into %LET or other statement.
So %embed(C:\project\query1.sql) is the same as %unquote(%fread(C:\project\query1.sql)).
Actually it is more like MODE=3.
%fread(C:\project\query1.sql,mode=3,eol=)
Yes, it is "more like MODE=3", except %embed macro is much simpler, requires only one parameter and does not rely on %FILEREF macro.
Hi Tom, thank you for your input. Yes, it is kind of similar, but, no, %embed(C:\project\query1.sql) is NOT the same as %unquote(%fread(C:\project\query1.sql)). By looking into the %FREAD macro implementation (MODE=1), I can see that this macro returns external file lines in form of %LET statements. After that, to embed the external file into SAS program one would need to reference all the created macro variables thus requiring another loop through all of them: &&W&i.
In my view %embed( ) macro function is much simpler and clearer solution, allowing to embed external code file in a single macro reference.
I really like this approach. It seems to me that this is not about deployment process, but more about something that can be called configurable jobs/transforms. Let's say an analyst can write several parametrized SQL templates that are used in a job, so the job is deployed once, but has several configurations (DeploymentJobs). Earlier I used OPEN and DOSUBL functions for such purposes, but EMBED macro seems more readable to me, definitely will try it.
Thank you, Sam, for your feedback. Your "configurable jobs" approach fits right in. Along this line of thinking you may also like Adaptive SAS programming for the Software Development Life Cycle.
Please let me know how these work out for you.
Thinking about, can we embed a BigQuery saved in GCP storage or on a remote server?
Hi Vivek,
If your file is stored in a location that can be accessed via URL, you can embed your file using FILENAME with URL access method, e.g.
filename extf url "https://domain.com/path/codefile.sql";%embed(extf)Hope this helps.
Wondering if a java class or jar file can be embedded into SAS
I am not aware of a one-line solution for this, but here are several resources which you may find helpful:
- Using the Java Object
- Java Programming for SAS Viya
- A Guide for Connecting Java to SAS® Data Sets
- Java in SAS®
Regarding calling R from PROC IML:
1. PROC IML has supported calling R since 2008, so this feature is much older than the other languages.
2. Your macro will not work inside the SUBMIT block in PROC IML. The contents of the SUBMIT block are not processed by the macro preprocessor. Rather, the contents are passed to R parser as a string. R doesn't know how to interpret "%embed(..)"
R (and other languages) have their own version of %INCLUDE. For example, in an R program you can use
source("C:/Temp/program.r")
to read the statements into an R program.
Thank you, Rick, for your feedback and explanation. I updated the R section embedding your clarification.
Rick,
Just for fun I did some experiments, and I think there is "a way" to push the code form file in SAS to R, of course it is very "non production" approach and a bit of "scratching your left ear with your right hand" but seems to work.
The code is below, it uses the macro I wrote in reply to Leonid's blog post.
Bart
filename f3 TEMP; data _null_; file f3; infile cards4; input; put _infile_ ';'; /* a "semicolon" trick for R statements separation */ cards4; Model <- lm(Weight ~ Height, data=Class, na.action="na.exclude") print (Model$call) print (Model) ;;;; run; proc iml; codeText = resolve(' %minclude(f3, source=1) '); print codeText; call ExportDataSetToR("Sashelp.Class", "Class" ); submit codeText / R; &codeText endsubmit; quit;Very nice! One restriction of this macro is that it depends on a file that must already be accessible from your SAS installation.
As a SAS developer using a (local) GIT repository, without SSH, you may be looking for a consistent way to get your arbtrary files into SAS.
Enter - the SASjs CLI!
Using this tool you can compile all your dependencies locally, so that you end up with a single-file-per-job (with all macros, includes, binary files etc included).
Binary files could include excel, zip, images, or even videos. Binary files are simply base64 encoded and inserted with PUT statements.
Here are the docs: https://cli.sasjs.io/compile/#3-binary-files
And here is an example of Thiago's SAS Rap encoded within a SAS web service (Stored Process or Viya Job): https://github.com/allanbowe/sasrap
Thank you, Allan, for your feedback and sasjs perspective. I am not sure I understand what binary files (images, videos) do within SAS code. Don't you mean inserting them into SAS output? Could you please clarify?
They are particularly useful in the context of deploying applications that will run on ANY SAS platform. By 'compiling' them into a Job (or Web Service) you can put your entire app into a set of Stored Processes (or Viya Jobs), which means there is nothing to deploy to the filesystem, and less things to 'break' in production. It also makes it easier to move an app from one place to another. And one less thing to 'arrange' (a filesystem) when deploying to a shared SAS environment.
Videos / sounds are silly examples, but images could be used for, say, producing a PDF report.
Thank you for the clarification. So you are basically packaging everything together. I merely talked about embedding a code from a file into code.