proc access dbms=oracle;

      /* Create the access descriptor and provide the ORACLE    */
      /* server connection information (username, password and  */
      /* ORACLE device driver information. store1 is the       */
      /* ORACLE SQL*Net designation of the ORACLE server.       */
   create sasuser.s1shoe.access;
      user=wagner;
      orapw=tennis;
      path=store1;
      table=shoe;

      /* Create a view descriptor based on the sasuser.s1shoe   */
      /* access descriptor.  Supply subsetting information to   */
      /* limit the items to only shoes (shoe ids are contained  */
      /* in the shoeparts table) but get information on all of  */
      /* the columns in the shoe inventory table (select all)   */
   create sasuser.s1shoe.view;
      select all;
      subset where invo in
         (select shoeid from shoeparts);

   run;

________________________________________________________________________

proc access dbms=as400;

     /* Create the access descriptor based on the AS/400       */
     /* collection clothing at the mail order site             */
   create sasuser.shoem1.access;
      collection=clothing;
      table=sptswear;

      /* Select just the columns from the access descriptor     */
      /* to extract just the shoe information.  For the mail    */
      /* order databases, shoe inventory numbers are 100-199.   */
   create sasuser.shoem1.view;
   select invno, wholepr,
      retpr, shsize, instock;
   subset where invo between 100 and 199;

run;

________________________________________________________________________

proc sql;

      /* Make the connection to the database */
   connect to db2(ssid=invt);

      /* Select the shoe information and the customer the */
      /* shoes were ordered for.  Instead of inventory id */
      /* the distribution center keeps manufacturer and   */
      /* style information in the orders table.           */
   create view sasuser.shoedc as
      select * from connection to db2
         (select manufact, style, size, custid from orders);

quit;

________________________________________________________________________

proc sql;

/* The table whouse.allshoes will contain information about      */
/* all of the shoes in The Sweat Shop inventory.  Here we        */
/* create the table and populate it with data from two of        */
/* the retail outlets.                                           */
create table whouse.allshoes like sasuser.s1shoe;

insert into whouse.allshoes
   select invo as shoetype, size, count, price
      from sasuser.s1shoe

   outer union corr

   select invo as shoetype, size, count, price
      from sasuser.s2shoe;

________________________________________________________________________

data whouse.inventry(drop=wholepr, invo);

         /* Include the information from the different sources.  */
         /* Variable names and inventory numbers are transformed */
         /* to the metabase definition                           */
      set whouse.allshoes (in=allshoe)
      sasuser.shoem1 (rename=(retpr=price
                             shsize=size instock=count));

         /* shoecnv. format will convert the disparate shoe       */
         /* inventory numbers to the format used by the metabase.*/
      if no allshoe then shoetype=put(invo, shoecnv.);

________________________________________________________________________

proc format;
   value shoecnv  100=27
                  101=42

   statements to convert other inventory item

run;

________________________________________________________________________

   select manufact, style, size, count(*)
      from sasuser.shoedc
      group by manufact, style, size;

________________________________________________________________________