Wade H. Van Buskirk, Pacific States Marine Fisheries Commission, Gladstone, Oregon

The method developed here for publishing data from SAS using a web server is known as the Computer Gateway Interface or CGI scripting method. CGI scripts normally operate in a batch nature in reaction to a web form submitted from a web browser. The script working in conjunction with the web server updates a frame or document on the browser. The update passed back to the web server from the CGI script can be generated by a batch SAS session.

Development of this technique was independent of the SAS/CGI and Web Broker in the SAS/Internet product and other available SAS CGI scripts. This method and the Web Broker can be distinguished by their ability to execute SAS programs on an independent computer known here as a "SAS server" somewhere on the Internet. Other methods of serving SAS data through the Internet such as with Java applets or some other client side application exist. These other methods technically operate more interactively with a SAS server to update the client web browser.

The purpose of this paper is to make the use of CGI scripts written in Perl and the supporting SAS programs for Internet publishing simpler. Although these were developed in UNIX, developers in other environments may also benefit.

This paper is written as a step by step guide to development of an interactive dynamic SAS Internet publishing system.

SAS has been the primary database manager for the Marine Recreational Fisheries Statistics Survey (MRFSS) since 1979. The National Marine Fisheries Service (NMFS) operated the database in the DOS operating system until 1994 when it was moved onto UNIX to improve access options and speed. The MRFSS is an extremely important and complex fisheries survey, which can be difficult to administer. Until 1996, all of the data were reported using large sets of printed tables, which were limited to only a few general summaries on an annual basis.

The recent explosion of internet use by government agencies provided an obvious information reporting advantage over printed publications for the MRFSS and fisheries data in general. The Pacific States Marine Fisheries Commission (PSMFC) acts as a fisheries data warehouse for the Pacific states. PSMFC established the Recreational Fisheries Information Network (RecFIN) to conduct the field collection of the MRFSS and centralize this with any other sport fishery data for the member states.

There are many cases of CGI scripts written in Perl (practical extraction and reporting language) to connect web servers with SAS. All the available CGI scripts found in this review were configured with SAS installed on the same machine as the web server. The purpose of this arrangement is to allow the CGI process to easily communicate with SAS via a shared environment. This shared environment includes variables, an inter-process communication (IPC) pipe and a local file system.

However, this happy arrangement of sharing the same machine is not always practical. The web server machine may already may be tasked with several other processing requirements from other projects and have insufficient storage space. It also may be more convenient to develop the SAS programs on a separate machine where the source data are normally processed. In our case, we have a single SAS license for one machine so cost of an additional license was a big factor.

An early CGI script written as a UNIX shell script (similar to a DOS batch file) by Larry Hoyle in 1994 started SAS on a web server machine. In this case, SAS did all the work of reading the environment, decoding what was requested and returning a product. Once the popularity of the web increased the use of Perl to write CGI scripts for UNIX was popularized.

Perl is a language developed for text pattern matching, file manipulation and process control that filled a niche between UNIX shell scripts and the C programming language. Perl is powerful, fast, is available for many operating systems and is free. Perl programmers have written many useful program libraries to assist in CGI scripting.

CGI scripts written in Perl normally re-code web server requests in a way that is more digestible to further processing. SAS programmers have used Perl to devise numerous methods to invoke SAS with a product request. These methods include either writing some SAS code to a file or adding more environment variables to be read by a SAS program. Early efforts used dedicated CGI scripts for each type of request or SAS program.

It is possible that one general-purpose CGI script can be written to start different SAS programs. We have found references to and copies of other general purpose CGI programs written in Perl. Most expect SAS to be installed on the web server machine in order to read a file or the environment and execute SAS. Some would require further modification to become truly general purpose. Five are documented on the Internet.

Development of this method was on Sun Sparc 5 workstations running Solaris 2.6, Netscape Enterprise Server 3.51, Perl 5 and SAS 6.12 on an TCP/IP intranet behind a firewall connected to a fractional T1 internet connection. Custom CGI security was implemented to allow only listed scripts to execute under the owners permissions (this function now available on many web servers). The secure shell was installed and tested.

SAS programs were debugged and tested in interactive SAS sessions. SAS product programming was written within a main macro to allow for use of global macro values. Web forms were developed using either Netscape Composer or Microsoft Office 97 and edited with a text editor.

The CGI program produced (sas.cgi) was made as general as possible to allow for a diversity of situations. Editing of text files was in Nedit on Solaris and in Windows using notepad. Graphics were captured in Lview Pro and pasted into MS Word.

The exploratory approach used here expects the target audience to be experienced with the SAS macro language, SAS language and functions and some Base Procedures. The audience would benefit from some experience with other computing languages such as Perl and the UNIX operating system. Experience with Hypertext Markup Language (HTML) is desirable.

The process of development is illustrated in steps with examples to show how each part of the CGI script works with SAS and the operating system. Explanations and comments are provided where necessary to assist in understanding the programming and illustrate operation. Similar steps could be followed to develop a solution on a different operating system.

The solution is derived in two converging tracks. First, we will develop the batch access to SAS from the command prompt. Second, we will develop the Perl script to invoke SAS from the command prompt. Then we will put the two together, implement its use in a browser and add more features.

This example data set will be used:

A main macro is used to control product options and supply default values for a table. The data are also filtered with a ‘where’ option. These defaults are assigned to global macro variables outside of the main macro.

The program is run in interactive mode and the product is examined for format and content in the output window.

(output window)

A title is added to the program and the output redirected to stdout (the terminal session window that started the SAS session)

When the program is run at this point, the table will appear in the terminal window instead of the output window.

Now the program can be saved (in this example as ‘bedrock.sas’) and run as a batch program that will return this product to the screen.

Now when we run SAS as a command and pass a new value to the program with ‘sysparm’ we get a different product.

This program can also be run locally or remotely using the standard ‘remote’ or ‘r’ shell commands. When this is done then the path to both the SAS executable and the SAS program must be included in the command line. The product is still output on the screen. (The last parameter below has line wrapped)

From a local directory command line:

From a remote directory command line:

The SAS program is now ready to be evoked from a Perl program.

When a web form is submitted a query string is produced from the data elements in the form. Each data element is composed of a name and a value expressed as an equality with a ‘=’. Each pair is further delimited by an ‘&’. This can be verified by submitting a form in your browser and examining the URL. The resulting pairs are based on the values selected in the form.

This example form will be used.

The form looks like this in a web browser with the select menu chosen to select ‘February.’ Note that the debug element is hidden from view.

When the form is submitted nothing happens because no program was specified in the form action, but the URL becomes:

This example Perl code will be used:

In this case, we have added the process id (pid) to the array and before printing a mini report for this query. Each CGI script started on the web server gets a separate process number. Each CGI process is a ‘child process’ of the web server ‘parent process’. This will become a convenient way to keep track of simultaneous queries.

We can run the Perl program from the command line by saving it to a file and making it executable.

Since the script will be looking for the query string in the environment, we will export one from this terminal session (parent process):

We can now run the Perl program to read the environment and produce output. We will produce a mini report on the form if we put a value in the name ‘debug.’ Having a flag like ‘debug’ is called a script function or a script control since it is trapped and acted on by the Perl program.

Putting the above HTML result into a file (check_debug.html) and reading it with a web browser produces a web page.

The out variable contains the ‘sysparm’ string we need to produce the SAS product programmed in the previous section.

Since we can produce a user defined product by using SAS by passing macro value declarations with a batch command. In addition, we can generate those macro values declarations from a Perl program by reading the query environment. Then we can connect the two and run the Perl program to get a user defined SAS product from a web query.

We need to get the Perl program to evoke the batch SAS program and pass the sysparm we built. This is done using the ‘system’ function in Perl.

We should check the environment for our query string we exported earlier.

Since the query is in the environment, we can run the Perl program to start SAS and pass the macro code.

We have changed the default ‘January" table to the user defined ‘February’ table using the environment variable we exported. When a web server starts a CGI script, it exports the query string automatically.

We can add another line of Perl code to inform the user that we are using the power of SAS to produce a report. Next we will put this script in the web server CGI area and run it from a web browser.

In this case, we are running the SAS program on a separate machine on the network using the ‘rsh’ command described earlier. The parts of the ‘rsh’ command have been broken out into separate variables to allow changes to be made from the query string and the resulting sysparm. The quoting for ‘sysparm’ was a little tricky in Perl. The HTML ‘<pre>’ tags were added to define the plain text tables as being pre-formatted with carriage returns.

Here is the Perl program ‘sas.cgi’ (now a CGI script) after our editing (blue portions show changes):

The script must be installed on a web server in the CGI bin in order to be executed. Scripts anywhere else will be downloaded instead of executed. This security feature prevents the browser from executing remote programs on itself and possibly causing damage to the file system.

The form action must be changed to point at the CGI bin and the CGI program to run.

Our form looks the same as before.

When we submit it now we get the URL defined in the form action with the query string added after the ‘?.’ We also get our combination Perl and SAS product page.

The hidden ‘debug’ field can be removed from the web page to eliminate the mini ‘CGI Values’ report.

At this point, we are ready to define the parts of the ‘rsh’ command using the CGI script by passing parameters from hidden fields in the HTML form. This will let us define the SAS server and SAS program to be used in the web. This will make the CGI program independent of the SAS server and the SAS program to be run.

First, we define some hidden fields in the HTML form to define the rsh command to be used in sas.cgi.

Now we can replace the rsh parameters we created in the last edit of sas.cgi:

With the associative array values that will be present when we submit the revised form bedrock.html.

We have separated the program path from the program name so that we can create a unique identifier for each query. The unique query identifier or ‘query reference’ will be the program name and the PID together (i.e. ‘bedrock12345’). We also add a parameter to define the path and name of the SAS log; in this case, we name it the ‘query refrence’.log

To make the sas.cgi output completely independent, we should move any HTML tags to the SAS program. To output HTML from SAS, we have written %comment. In %comment we make a global macro variable called ‘&options’ to hold the default options statement. When a text string is passed to the %comment macro, we temporarily change the options so no SAS messages are displayed. Then, we redirect log output to the fileref ‘out’, the UNIX stdout device with long records, and put the text string. Then the log is directed back to normal and the default options are reinstated. This method avoids the limitation of 256 characters in a data step ‘put’.

Above we have changed the title to HTML level 3 title format and opened a preformatted area with the ‘pre’ tag for the SAS table. Then we close the ‘pre’ tag. We have also used %comment to add a hyperlink to an email message for sending a comment. We have set the subject of the email to the ‘query reference’ (program name + process id). When we submit this form now we get back a nicer product.

We also have a way for the user to make a comment to that specific query ‘bedrock25933’ so we can check the SAS logs for ‘bedrock25933.log’ to see if there was a problem.

To show the date, time and machine that requested the query along with the query hyperlink, we read the web server environment and call the system clock.

When we open the SAS query log file in a web browser we see an entry for each query made through sas.cgi. Each entry has hyperlinks to reproduce the query and view the SAS log. The HTML header and title for the query file were set when the queries.html file was created.

When we click on the link to the log we can view the SAS log in the web browser and search the contents.

The form now has a title, a new hidden object and a new visible object for selecting the type of product.

The response for the web server when the product type is ‘Dataset’ now contains a hyperlink to the SAS transport file.

Shift-clicking the file opens the ‘save as’ dialog with the default name of the file. The default name is the query reference with the appropriate extension.

The final versions of the code used in these examples is located on the authors website.

Most sites connected to the Internet operate their servers behind firewalls due to security concerns. The firewall is effective at filtering protocols and services in either direction between the local Intranet and the Internet at large. It would be a breech of security to allow incoming rsh commands to penetrate the site as this could be an avenue of attack by hackers. Most sites would have both the web and SAS servers behind a firewall. Standard remote commands are adequate in most cases. There is an option for allowing secure access to remote machines across the Internet.

Standard remote shell commands (rsh, rcp, rdist, etc.) within an intranet between trusted machines that rarely go down are secure from all but the most powerful attacks from hackers on the internet. UNIX machines typically come with the remote shell server or ‘daemon’ running on the host server. Trusted client machines are listed in a file on the server and are checked agents the identity of the requestor when the remote shell server receives a request.

The secure shell standard (ssh, scp, sdist, etc.) can operate in a secure manner in an Intranet and out on the Internet. It uses the RSA security protocol with private and public encryption keys. The Client machine provides a public key to the server when the service is set up. During connections the keys are used to validate the initial connection and triple DES encrypt the contents of the data.

The secure shell is not vulnerable to listening, snooping and IP, DNS or router spoofing. The default keys are currently up to 1024 bits and sustained connections are renegotiated with new random encryption keys hourly. Only a very powerful external attack or internal tinkering could compromise the private key of a client. Passcode access is an option for increased security. Brute force deciphering of the private keys or packets is impossible in a human lifetime with current technology.

The sas.cgi program saved us money and time. This technique has saved the project, and ultimately the public, the cost of annual licenses for SAS/Internet. It has also given us the opportunity to utilize, to our advantage, control of communication between the web server and the SAS server. The Perl language has allowed us to develop important tools and methods for monitoring query activity and enhancing SAS output more easily then programming in SAS.

Time is saved in development and debugging of new products. SAS development can be done interactively using global macros before or after the web interface is designed. Further CGI programming is not required since sas.cgi does not need to be modified for new SAS products. Development and production queries occur in the same data environment on the SAS server, which makes for rapid location and repair of production problems.

The batch nature of web server to browser interaction on the Internet is conducive to the use of SAS batch sessions to produce page content. Other methods such as Java applets can operate within a browser to produce page content with an event driven model. Both methods can be used to produce an interactive dynamic interface to SAS data and products.

SAS can be used to produce ‘hidden’ data structures in HTML and JavaScript. These SAS derived data structures can be read using JavaScript to populate web form objects dynamically. This would probably be slower then a Java Applet communicating with a SAS/Share*Net server, but the cost of development would be less and JavaScript is much simpler to program then Java with sockets. When using this CGI method all the socket connection details are taken care of by the web server and the remote commands.

The development of a general-purpose sas.cgi with associated techniques has greatly reduced the complexity of our previous Internet publishing system. The Internet publishing system has nearly eliminated the need for hardcopy distributions. The ability to rapidly develop web based applications in SAS allowed the production of many more types of products then would be the case with lower level languages. Many of our existing SAS programs were quickly converted to produce web content.

Some limitations in HTML forms can be overcome with JavaScript on the client side. Although Perl is not a simple language it can be characterized that, like SAS, it is very powerful for what is was designed to do. We hope that general-purpose CGI scripts for SAS will allow SAS programmers to concentrate on SAS, HTML and perhaps JavaScript in their development efforts.

The use of our data in fisheries management has increased in importance and is becoming more widely used and respected. The system currently averages about 20 queries per day.

Van Buskirk, Wade H. Internet Publishing with SAS/BASE using Perl in a UNIX Environment Proceedings of the Pacific Northwest SAS Users Group, June 11, 1998