- [The SQLinDS package [ver. 2.2]](#sqlinds-package)
- [Content description](#content-description)
  * [library `dsSQL`](#library-dssql)
  * [`%dsSQL_inner()` macro](#dssql-inner-macro)
  * [`%SQL()` macro](#dssql-inner-macro)
  * [`dsSQL()` function](#dssql-function)
  * [License](#license)
  
---

# The SQLinDS package [ver. 2.2] <a name="sqlinds-package"></a> ###############################################

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)*.

Copy of the article is available at:
[https://support.sas.com/resources/papers/proceedings12/004-2012.pdf](https://support.sas.com/resources/papers/proceedings12/004-2012.pdf)

Package provides ability to *execute* SQL queries inside a data step, e.g.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
  data class;
    set %SQL(select * 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)

---

Package contains: 
1.   libname    dssql 
2.   macro      dssql_inner 
3.   macro      sql 
4.   function   dssql 

Required SAS Components: 
  *Base SAS Software*

*SAS package generated by generatePackage, version 20211111*

The SHA256 hash digest for package SQLinDS: 
`F2BE3CC68C9A34DD324FD35C8287A4F89737E2E28E806E0DD7FB0EBF60F7C569` 

---
# Content description ############################################################################################


## >>> library `dsSQL`: <<< <a name="library-dssql"></a> ########################

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

If possible a subdirectory of the `WORK` location is created, like: 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
LIBNAME dsSQL BASE "%sysfunc(pathname(WORK))/dsSQLtmp";
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

if not possible, then redirects to the `WORK` location, like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
LIBNAME dsSQL BASE "%sysfunc(pathname(WORK))"; 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
---



## >>> `%dsSQL_Inner()` macro: <<< <a name="dssql-inner-macro"></a> #############

**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.

Recommended for *SAS 9.3* and higher. 

---



## >>> `%SQL()` macro: <<< <a name="dssql-macro"></a> ###########################

The **main** macro which allows to use 
SQL queries in the data step.

Recommended for *SAS 9.3* and higher.

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](https://support.sas.com/resources/papers/proceedings12/004-2012.pdf)

### SYNTAX: ###################################################################
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
%sql(<nonempty sql querry code>)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The sql query code is limited to *32000* bytes.

### EXAMPLES: #################################################################

**EXAMPLE 1**: simple SQL query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
data class_subset;
  set %SQL(select name, sex, height from sashelp.class where age > 12);
run;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**EXAMPLE 2**: query with dataset options
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
data renamed;
  set %SQL(select * from sashelp.class where sex = "F")(rename = (age=age2));
run;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**EXAMPLE 3**: dictionaries in the data step
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
data dictionary;
  set %SQL(select * from dictionary.macros);
run;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
---


 
## >>> `dsSQL()` function: <<< <a name="dssql-function"></a> ####################

**Internal** function called by the `%SQL()` macro.
The function pass a query code from the `%SQL()`
macro to the `%dsSQL_Inner()` internal macro.

Recommended for *SAS 9.3* and higher. 

### SYNTAX: ###################################################################
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
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.

---

## 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.

---
