sqlinds/sqlinds.md

Documentation for the SQLinDS package.

---

SQL queries in Data Step

---

Version information:

• Package: SQLinDS
• Version: 2.3.1
• Generated: 2025-11-22T12:47:32
• Author(s): Mike Rhoads (RhoadsM1@Westat.com), contributor Bartosz Jablonski
• Maintainer(s): Bartosz Jablonski (yabwon@gmail.com)
• License: MIT
• File SHA256: F*606A24A2A6B06DAAD2D443FA9A9819D9564235A5CD8599FD15586F1EFFCB41BC for this version
• Content SHA256: C*4CCCF31DA9D94E0EE2DA612724D395056B7BA07CB593C93947835BB8319B33EB for this version

---

The SQLinDS package, version: 2.3.1;

---

The SQLinDS package [ver. 2.3.1]

The SQLinDS package is an implementation of the macro-function-sandwich concept introduced in the "Use the Full Power of SAS in Your Function-Style Macros", the article by Mike Rhoads (Westat, Rockville).

The article is available at: https://support.sas.com/resources/papers/proceedings12/004-2012.pdf

Copy of the article can also be found in additional content directory.

Package provides ability to execute SQL queries inside a data step, e.g.

  data class;
    set %SQL(select name, age from sashelp.class);
  run;

See the help for the %SQL() macro to find more examples.

Content

SQLinDS package contains the following components:

1. %SQL() macro - the main package macro available for the User
2. dsSQL() function (internal)
3. %dsSQL_inner() macro (internal)
4. Library DSSQL (created as a subdirectory of the WORK library)
5. Optional KMF-abbreviations sqlinds

---

---

---

Required SAS Components:

• Base SAS Software

---

---

Package contains additional content, run: %loadPackageAddCnt(SQLinDS) to load it or look for the sqlinds_AdditionalContent directory in the packages fileref localization (only if additional content was deployed during the installation process).

---

SAS package generated by SAS Package Framework, version 20251122, under WIN(X64_10PRO) operating system, using SAS release: 9.04.01M9P06042025.

---

The SQLinDS package content

The SQLinDS package consists of the following content:

1. dssql libname

2. %dssql_inner() macro

3. %sql() macro

4. dssql() function

5. sqlinds kmfsnip

6. License note

---

dssql libname

The dsSQL library stores temporary views generated during the %SQL() macro execution.

If possible a sub-directory of the WORK location is created, like:

LIBNAME dsSQL BASE "%sysfunc(pathname(WORK))/dsSQLtmp";

if not possible, then redirects to the WORK location, like:

LIBNAME dsSQL BASE "%sysfunc(pathname(WORK))"; 

---

---

%dssql_inner() macro

The %dsSQL_Inner() macro is an internal macro called by dsSQL() function.

The macro generates a uniquely named SQL view on the fly which is then stored in the dsSQL library.

The %dsSQL_Inner() is not designed to be called on its own.

Recommended for SAS 9.3 and higher.

---

---

%sql() macro

The %SQL() macro is the main macro in the package. The macro allows to use SQL queries in the data step.

Recommended for SAS 9.3 and higher.

Implementation is based on the article: "Use the Full Power of SAS in Your Function-Style Macros" by Mike Rhoads (Westat, Rockville), available at: https://support.sas.com/resources/papers/proceedings12/004-2012.pdf

Copy of the article can also be found in additional content directory.

SYNTAX:

%sql(<nonempty Proc SQL query code>)

The THE query code is limited to approximately 32000 bytes.

EXAMPLES:

EXAMPLE 1: simple SQL query

data class_subset;
  set %SQL(select name, sex, height from sashelp.class where age > 12);
run;

EXAMPLE 2: query with dataset options

data renamed;
  set %SQL(select name, age from sashelp.class 
           where sex = "F")(rename = (age=age2)
           );
run;

EXAMPLE 3: Proc SQL dictionaries in the data step

data dictionary;
  set %SQL(select dict.* from dictionary.macros as dict);
run;

---

---

dssql() function

The dsSQL() function is an internal function called by the %SQL() macro.

The function pass a query code from the %SQL() macro to the %dsSQL_Inner() internal macro.

The dsSQL() is not designed to be called on its own.

Recommended for SAS 9.3 and higher.

SYNTAX:

dsSQL(unique_index_2, query)

Arguments description:

1. unique_index_2 - Numeric, internal variable, a unique index for views.

2. query - Character, internal variable, contains query text.

---

---

sqlinds kmfsnip

This is a help note for sqlinds KMF-abbreviation.

The snippet presents a template for use of the %SQL() macro.

To read help info about the macro run he following: %helpPackage(SQLinDS,'%sql()')

---

---

License

Copyright (c) 2012 Mike Rhoads

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

---