I have used the SAS/Access Interface to OLE DB for years. In the past I would capture data from a relational database management system (RDBMS) into a SAS data set and then join that remote data to a local SAS data set using another proc or data step.
Having to read the data more than once seemed inefficient, so I attempted to reference the pass-through data as a derived table (aka in-line view) and join that data directly to a SAS data set. Sure enough, as the below code using SQL Server Express shows this can be done.
In the simplified example below, a SAS data set named SASCAT is created containing the groupname and an abbreviated name (abbrname). After that, an OLEDB connection is made to the freely downloadable SQL Server Express database.
The derived table consists of the SQL statements contained between the parenthesis starting with the FROM CONNNECTION TO OLEDB keywords. That derived table is referenced as SQL which is then joined to the SAS data set sascat which is referenced as SAS and combined as an inner join using the ON clause.
%macro dbconnect( server= ) ; /* DSN-less OLEDB connection */ connect to oledb (init="Provider=SQLNCLI11;Integrated Security=SSPI;Data Source=&server.") ; %mend ; %macro dbdisconnect() ; disconnect from oledb ; %if &sysdbrc ne 0 %then %put %str(E)RROR: %superq( sysdbmsg ) ; %mend ; data sascat ; input groupname & $50. abbrname : $4. ; datalines ; Executive General and Administration Exec Inventory Management Imgt Manufacturing Mfg Quality Assurance QA ; run ; proc sql ; %dbconnect( server = localhost\SQLExpress ) create table results as select sas.abbrname , sql.count from connection to oledb ( select groupname , count( groupname ) as count from adventureworks2014.humanresources.department group by groupname ; ) sql join work.sascat sas on sql.groupname = sas.groupname ; %dbdisconnect() quit ;