Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

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;

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.