/* Create library reference to permanent SAS data library          */
libname perm 'c:\sas\lib1';

proc sql;

    /* Establish connection to RDBMS                                  */
 connect to odbc(dsn="ORACLE",uid=scott,pwd=tiger);

       /* Build a SAS data file named t_group1 that contains all      */
       /* tables associated with group1 of the organization. Use the  */
       /* the special ODBC API call SQLTables.                        */
    create table perm.t_group1 as
       select *
       from connection to odbc (ODBC::SQLTables, "group1");

       /* Disconnect from the database                                */
    disconnect from odbc;

quit;

   /* Use the T_GROUP1 SAS data file to build a macro list of table   */
   /* names, and collect the total number of tables. This information */
   /* is used by the ISEARCH macro in the next step.                  */    

data _null_;
   set perm.t_group1(end=last);
   call symput("tab" ||left(trim(_n_)), '"' ||
               tab_name || '"' );
   if last then
      call symput(num_tab,_n_);
end;

   /* The ISEARCH macro uses a macro list of tables and the total     */
   /* number of tables for T_GROUP1 to generate PERM.O_INDEX, a SAS   */
   /* data file containing one row for each primary key in the ORACLE */  
   /* database for GROUP1.                                            */                                                     
%macro isearch;

      /* Execute one time for each table                              */
   %do i=1 %to &num_tab;

         /* Use proc sql to locate primary keys                       */
      proc sql;

            /* Establish a connection to the ORACLE database          */
         connect to odbc(dsn="ORACLE",uid=scott,pwd=tiger);

            /* Create a temporary SAS data file containing primary    */
            /* key information for a single table within the database */
         create table temp as
            select *
            from connection to g1 (ODBC::SQLPrimaryKeys , , tab&i);

            /* Disconnect from the ORACLE database.                   */
         disconnect from i1;

      quit;

         /* Append at each iteration of the ISEARCH macro, new        */
         /* primary key information to PERM.O_INDEX.                  */
      proc append base=perm.o_index data=temp;
      run;

    %end;


%mend;