Using Database-Specific SQL Features in SAS
Pass-through mode in Proc SQL is most often used to improve efficiency, but that is not the only benefit it can bring. It can also make available to the SAS user SQL syntax which is not normally available in SAS, but which is supported by the external database. This example demonstrates the use of a TRANSFORM statement - which is not in SAS's own repertoire - to query a Microsoft Access table.
proc sql; connect to access as ourdb(path=".datasqltest.mdb"); select * from connection to ourdb (TRANSFORM sum(value) AS total SELECT store FROM acc_music GROUP BY store PIVOT year); disconnect from ourdb; quit;
The inner query - the five lines from TRANSFORM to PIVOT - is processed by Microsoft Access, and can therefore use any syntax which is meaningful to Access, whether SAS understands it or not. Here, SAS's Advanced Editor does not recognise the TRANSFORM and PIVOT keywords - which are SQL extensions specific to Jet SQL, the dialect used by Access - so leaves them coloured black. (They provide functionality similar to pivot tables in Excel.) The query is processed successfully, returning output that looks something like this:
store 2007 2008 Belfast 129751 156998.7 Cardiff 172736.1 210739.3 Edinburgh 130379.2 158069.9 London 517928.4 633975.3
Similarly, pass-through's EXECUTE facility can be used to forward any SQL statement meaningful to the external database.
The only "catch" with all this is that, if you want to use syntax that is not known to SAS, you have to be in explicit pass-through mode, using CONNECT and DISCONNECT statements (rather than a LIBNAME) to interface to the external database.
In SAS 9.2 the SQL option IPASSTHRU - "implicit pass through" - was introduced, and became the default, but implicit pass-through will only handle standard SAS SQL syntax. If you want to do anything non-standard, you still have to code your pass-through in the traditional way, as above, with explicit CONNECT and DISCONNECT.