Best practice on reading SAS datasets for outside system consumption

[jplarios]

Hi:

Let's say with DIS a data mart was created where the SAS dataset was in a SAS Engine. How would something like PHP(an example) access these? So instead rather then push this data mart into some DBMS, how to make the outside system access this?

Thanks,

jp

[MikeMcKiernan]

For Windows, there's the SAS Drivers for ODBC and the SAS Providers for OLE DB.  I believe that both of those options (certainly ODBC) will require a running instance of SAS to act as a server.  I'd think that's an option if you were considering accessing a database server.  I'm sure there are lots more options.

[LinusH]

And there is of course JDBC...

For these options you need a SAS server running, and if nothing has evolved in this area I think you need a SAS/SHARE server, and a license for SHARE*NET.

Another approach is to use programming against (width?) the Intergated Object Model, which is available though Integration Technologies product. See on-line doc for more info about that.

/Linus

[CameronL]

Stored Processes can be used and if using a browser based interface it generally works well.  Just don't try and return a million row dataset etc. all at once.  You will need JBOSS or some other server running however.  Using this method is really easy with something like jQuery for example

$.ajax({
   url:'your stored process url',
   data:'param1=foo&param2=bar',
   success: function(results){
     $('#results').append(results);
   },
   error: function(xhr){
     $('#results').append('I'm sorry, I can't do that Dave.');
   }
});

Stored processes can be set up as Web Services as well and then consumed by your web application (data is returned as xml).

PHP in windows allows the use of Active X Data Objects via COM.  I did a quick test with the below snippet and it works.  You need to ensure you have loaded COM modules for PHP (I don't think they load by default).  The basic structure is the same as using something like ASP. When I tested this, I found I need to execute a libname statement prior to the SQL.  To see the return xml from the workspace server use the debugging options at the top of the script sample;

//Debugging turned on in the PHP script as opposed to in PHP.ini.  Remove when complete.
ini_set('display_errors', 1); 
ini_set('log_errors', 1); 
ini_set('error_log', dirname(__FILE__) . '/error_log.txt'); 
error_reporting(E_ALL);
//Set up the ADO Connection
$conn = new COM("ADODB.Connection") or die("Cannot start ADO.  Check your server has the SAS Drivers installed."); 
// SAS Remote IOM Server Connection.
$conn->Open("Provider=SAS.IOMProvider.9.2;Data Source=iom-bridge://<<SERVERNAME>>:8591;User ID=<<USERNAME>>;Password=<<PASSWORD>>");
// Execute the SAS Code here.  For SQL, no need to use PROC SQL.  Libnames need to be defined first.  Can't see why datastep, procs etc couldn't be executed also.
$rs = $conn->Execute("libname <<LIB>> '<<FILEPATH>>'");
$rs = $conn->Execute("SELECT <<VARS>> FROM <<LIB>>.<<DATASET>>");
// Display all the values in the records set
echo "<p>The Below Data is from SAS.</p>";
 while (!$rs->EOF) { 
    $fv = $rs->Fields("<<VARS>>");
    echo "Value: ".$fv->value."<br>\n";
    $rs->MoveNext();
} 
$rs->Close();

Message was edited by: Cameron Lawson Added additional step in sample code after testing.