Helpful Hints on Developing a User Friendly Database

Developing an effective database application requires an interface that is easy for the user. This paper will explore the features of SAS/AF and methodologies of building a successful database. It combines user interface suggestions for the front end while also suggesting back end SCL, SQL and data step logic that makes the software efficient to program and to operate. The majority of the examples are technical tips but there are also shared lessons learned from collaborating with end users which prove to be very important in creating an effective application.

There are many solutions for creating a data entry system ranging from a simple Excel spreadsheet to a sophisticated Oracle database. Each set of technologies works well for a specified task. This paper will explore a database containing clinical information used in regulatory submission. SAS/AF is very suitable for this since all analysis work for clinical data requires SAS. The scenario of this particular project involved a pharmaceutical company developing a custom database. They had already licensed BASE SAS with very little additional modules. One advantage of SAS/AF is that it can be rolled out to clients that do not have SAS/AF during execution. In this scenario, SAS was installed on a Windows server.

SAS was delivered to users on their desktops through terminal services. This was economical since there was only one SAS license required. Even though SAS/AF was the main software used during development, the programming was not all in SCL, but also involved SQL and data step logic. It is useful to use SQL and data step logic where ever possible, so that more SAS programmers can understand and maintain the system in the future. The main reason the client chose a customized solution was because of unique requirements. It would have made an off-the-shelf system, such as Oracle Clinical, a large investment in infrastructure and operational procedure change. It was therefore more effective to develop tools specific to the specific requirements. Even though the data entry system, named “BigRed”, was a custom effort, modularization and the use of a data driven approach made the process much more efficient.

Data Driven
One of the biggest time savings in development was to drive the behavior of the data entry system through SAS datasets. The data tables which contain the actual clinical data being entered were more than just data repositories. They also acted as a collection of metadata which drives the labels on the screens. This is a data step example which was used to create the initial data.

*** Define the Treatment Center ***;
data dbdata.trtcent (label="Blood Collection at Treatment Center" read=&password genmax=11);
attrib srcloc label="Collection Source
Location" length=8
unitid label="Blood Unit ID"
grprh label="Blood Group and Rh"
length=8 format=BLOODFMT.
pcode label="Test or Control"
length=8 format=PCODEFMT.
coldate label="Collection Date"
length=8 format=mmddyy6.
usrname label="User Name"
datetime label=
"Date Time of User Interaction"
length=8 format=DATETIME13.;

The data table was defined with a label of “Blood Collection at Treatment Center”. This label was later used in a selection list for table selection during data entry. Each variable also contained labels. These were used for data entry screen variable selections and labels. The user defined formats were stored in a formats catalog. The values of these were used as coded values for pull down menu selections. At first glance, the data being defined appears to be purely for storing information. For efficiency, these same attributes also affected the user’s selection choices on the data entry screens.

One additional layer of security that was easily implemented was to password protect the data table. In this example, it prevented users from reading the data directly by the option (read=&password). It forces the users to use the data export or other reporting engines through the system. This allowed better control over what variables were delivered to users such as excluding unnecessary administrative variables.

complete paper at "Helpful Hints on Developing a User Friendly Database with SAS/AF " and related Clinical Data Management.
Bookmark and Share


Popular posts from this blog

Clinical Trials Terminology for SAS Programmers

Remembering Dad