SAS/CONNECT User's Guide

Example 13. DTS: Combining Data from Multiple Remote Sessions

Purpose

Using SAS/CONNECT to establish links to multiple remote hosts, you can access data on several hosts, draw that data together on the local host, and analyze the combined data. For example, if you have data that is stored under OS/390 in a DB2 database and related data that is stored in an ORACLE database under UNIX, you can use SAS/CONNECT in combination with SAS/ACCESS to combine that data on your local host. This example uses salary and employee data gathered from two remote hosts to illustrate the process.

Program

This example signs on to two remote hosts, downloads data from both hosts, and performs analyses on the local host. The program uses the SIGNON and RSUBMIT statements. Therefore, it can be run from a line-mode session as well as from the windowing environment.

Note: Bullets [2] through [5] apply to downloading both DB2 and ORACLE data. [cautionend]

      /*************************************/
      /* establish link to OS/390          */
      /*************************************/
[1] options comamid=ehllapi;
   filename rlink 
      '!sasroot\connect\saslink\logtso.scr';
   signon a;
      /*************************************/
      /* download DB2 data using           */
      /* SAS/ACCESS view                   */
      /*************************************/
[2] rsubmit a;
[3] libname db 'app.db2.views' disp=shr;
[4] proc download data=db.employee 
      out=db2dat;
   run;
[5] endrsubmit;

      /*************************************/
      /* establish link to UNIX            */
      /*************************************/
[6] options 
      remote=hrunix comamid=tcp;
         filename rlink 
            '!sasroot\connect\saslink\tcpunix.scr';
         signon;

      /*************************************/
      /* download ORACLE data using        */
      /* SAS/ACCESS view                   */
      /*************************************/
[2] rsubmit hrunix;
[3]    libname oracle '/hr/emp/records/';
[4] proc download 
      data=oracle.employee out=oracdat;

   run;
[5] endrsubmit;

      /*************************************/
      /* sign off both links               */
      /*************************************/
[7] signoff hrunix;
   signoff a cscript=
      '!sasroot\connect\saslink\logtso.scr';

      /*************************************/
      /* join data into SAS view           */
      /*************************************/
[8] proc sql;
   create view joindat as
      select * from db2dat, oracdat
      where oracdat.emp=db2dat.emp;

      /*************************************/
      /* create summary table              */
      /*************************************/
[9] proc tabulate data=joindat 
      format=dollar14.2;
      class workdept sex;
      var salary;
      table workdept*(mean sum) all,
      salary*sex;
      title1 'Worldwide Inc. Salary Analysis 
              by Departments';
      title2 'Data Extracted from Corporate 
              DB2 Database';
   run;

/* display graphics */
[10] proc gchart data=joindat;
      vbar workdept/type=sum
         sumvar=salary
         subgroup=sex
         ascending
         autoref
         width=6
         ctext=cyan;
      pattern1 v=s c=cyan;
      pattern2 v=s c=magenta;
      format salary dollar14.;
      title1 h=5.5pct f=duplex 
          c=white
         'Worldwide Inc. Salary Analysis';
      title2 h=4.75pct f=duplex 
         c=white
         'Data Extracted from Corporate DB2 
          Database';
   run;
   quit;

	To sign on to a remote host, you need to provide several items of information: the remote-session id, which is specified in a REMOTE= system option or as an option in the SIGNON statement. the communications access method, which is specified by using the COMAMID= system option in an OPTIONS statement. the script file to use when signing on to the remote host. This script file is usually associated with the fileref RLINK. Using this fileref is the easiest method for accessing the script file. When you have provided all of the necessary information, you can submit the SIGNON statement. You can specify the remote-session id in the SIGNON statement. If you omit the remote-session id from the RSUBMIT statement, the statements are submitted to the remote session that was identified most recently in a SIGNON statement, an RSUBMIT statement or command, or in a REMOTE= system option.
	After you have established links to two or more sessions, you can remote submit statements to any of the remote hosts by simply identifying in the RSUBMIT statement which host should process the statements. When the remote-session id has been specified by a previous statement or option, you are not required to specify the remote-session id in the REMOTE statement. This example includes the remote-session id in the RSUBMIT statements, even when the remote-session id is not required, to clarify which host is processing each group of statements.
	Associate a libref with the library that contains the SAS/ACCESS view of the database on the remote host.
	The SAS/ACCESS view can then be downloaded to the local host. Note that when you download a view of a database, a temporary SAS data set is materialized from the view and downloaded to the local host. In this example, the output data set on the local host is a temporary SAS data set.
	The ENDRSUBMIT statement ends the block of statements (named in the previous RSUBMIT statement) that are submitted to the remote host.
	To establish a second remote session, re-set the REMOTE= and COMAMID= options to values that are appropriate for the second host. You also need to reset the fileref RLINK to associate it with the script file for the second remote host.
	Terminate the links to both the UNIX remote host and the OS/390 remote host. Use the CSCRIPT= option to identify the script file for signing off the OS/390 host.
	On the local host, you can now use the SQL procedure to join into a single view the two SAS data sets that were created when you downloaded the views from the remote host.
	To analyze the joined data, use the name of the view on the local host in a PROC TABULATE step.
	[10] If you have SAS/GRAPH on your local host, you can also use graphics procedures to analyze the view that is created from the two remote databases.

Chapter Contents
Previous
Next
Top of Page