Monday, November 26, 2007

Demarcation

Good systems practice is to have demarcations between systems layers. This involves separation of the following layers (at a minimum):

Application
Business logic
Data access
Data

SAS programmers though tend to muddle all together:

libname indata ...; <-- Data Access
data mydata...; <--- Business Logic
ods html; proc report...; <--- Application Layer / UI

A better way to handle is to treat all layers as standalone and get them out of each other's space. The easiest way for a SAS coder to accomplish this is to use macro libraries to get it all started:

%GetFinanceData() ;
%DetermineProfit() ;
%OutputResults() ;

However, even this is constrained since everything is called together and a single program controls what happens top-to-bottom. Rather than doing the final piece as a part of the batch job, consider doing it on demand and perhaps through other means:

%GetFinanceData() ;
%DetermineProfit() ;

...wait until use requests information....

Get information on demand and make the presentation logic extraneous to SAS. Someone could even keep it in SAS ODS but the idea would be to pull it out to an application layer that is completely separated from how the information was formulated.

SAS programmers would be better off putting in extremely strong lines of demarcations between layers. This makes code easier to maintain, easier to change, and easier to understand.

Longer term, web service calls will simplify this even more but the concept of processing silos can be done now. Break your code apart so everything isn't glued together so tightly that code reuse is hard and code libraries are non-existent or little used.

C# and DBF files

I have a client that asked me to read DBF files generated by their SAS application. Well, this posting is to explain to someone what i have learned in reading DBF files using C#. The main thing I learned was no spaces in the file path.

Here is the code that worked for me:

string connectionString = "Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=C:\projects\Client\Data\WeeklyAvailableComparison;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
string selectCommand = @"SELECT * FROM C:\projects\Client\Data\WeeklyAvailableComparison\ac_141.dbf";
DataTable dt = new DataTable();

OdbcConnection oConn = new OdbcConnection();
oConn.ConnectionString = connectionString;
oConn.Open();
OdbcCommand oCmd = oConn.CreateCommand();
oCmd.CommandText = selectCommand;

dt.Load(oCmd.ExecuteReader());

I tried doing a 8.3 conversion and it wouldn't work for the select clause.

I hope this helps someone.

Alan

SAS throwing RPC error

If you are doing code in C#  and get this error when creating a LanguageService: The RPC server is unavailable. (Exception from HRESULT:...