/***********************************************************/
/* ODBLOAD.SAS contains two parts.  The first part         */
/* is a macro that generates PROC SQL EXECUTE              */
/* statements and stores them in a file.  The              */
/* part contains the PROC SQL statement, connects          */
/* to the database via ODBC and executes the               */
/* EXECUTE statements to create the ODBC table             */
/***********************************************************/

   /* %ODBLOAD macro creates a table definition            */
   /* (optionally), and the necessary INSERT INTO          */
   /* statements to load an ODBC table from a SAS          */
   /* data set.                                            */
%macro odbcload(
   data=_last_,          /* SAS data set to load from.     */
   create=Y,             /* Y to define the table.         */
   table=DataBaseTable,  /* database table name.           */
   connect=connect,      /* the connection name used.      */
   sqlcode='loader.sas', /* a valid file name.             */
   debug=Y);


   %local tmpmod;        /* Stores file access mode.       */
   %let tmpmod=;

   /* Now, create the temporary data set _ODBC_ which      */
   /* contains the structure and characteristics of        */
   /* the SAS data set, &DATA.                             */
proc contents data=&data noprint out=work._odbc_;
run;
 
   filename sqlcode &sqlcode;

   /* Use the _ODBC_ data set to generate the table        */
   /* definition.                                          */
data _null_;

   %if &create=Y %then 
      %do;
         %let tmpmod=mod; 
         file sqlcode &tmpmod;  /* Open file for write.    */
      %end;

   set work._odbc_ end=end;
   length puttext $200;
   puttext="";
   length comma $4;
   comma=" ','";
   retain putnum 0;
   %if &create=Y %then 
      %do;
         if _n_=1 then 

   /* Write to the file the first EXECUTE statement        */
   /* to create the table.                                 */

            put ///"execute ( create table &table ( ";
         else 
            put ",";
      %end;
         if end then 
            comma="";

   /* The PROC CONTENTS variable TYPE states if a SAS      */
   /* variable is numeric (type=1) or character            */
   /* (type=2).                                            */
   /*                                                      */
   /* The PROC CONTENTS variable NAME contains the         */
   /* name of the SAS variable.                            */

         if type=1 then   
            do;
               %if &create=Y %then 
                  %do;
                     put +8 name "number" @;
                  %end;
               puttext = "_chartmp=put(" || trim(name) || ", 
                  best16.);";
            end;
         else 
            do;
               %if &create=Y %then 
                  %do;
                     put +8 name "character (" length ")" @;
                  %end;
               puttext = "_chartmp=quote(" || trim(name) || 
                  ");";
            end;

   /* If it is the end of list of variables in &DATA,      */
   /* terminate the EXECUTE statement with BY              */
   /* CONNECT; otherwise, just write out the list of       */
   /* the variables that have been collected so far        */
   /* in the PUTTEXT variable and their length from        */
   /* PUTNUM.                                              */
 
         %if &create=Y %then 
            %do;
               if end then 
                  put +8 ") ) by &connect;";
            %end;
         putnum + 1;
         puttext = trim(puttext) || " put _chartmp " || 
            comma || " @;";
         call symput('PUT'||left(put(putnum, 6.0)), 
            puttext);
         file log;
         put putnum= puttext=;
         if end then 
            call symput('PUTNUM', left(put(putnum, 6.0)));
run;

   /* Create the EXECUTE INSERT statements to load         */
   /* the table with values from the data set, &DATA.      */
data _null_;
   file sqlcode &tmpmod;
   set &data;
   length _chartmp $200;
   if _n_=1 then 
      put ///;
      put "execute ( insert into &table values (" @;
   %do i=1 %to &putnum;
      &&&put&i
   %end;
      put ") ) by &connect;";
run;
%mend;   /* End of macro ODBCLOAD.                         */

   /*  The LIBNAME statement below designates the          */
   /*  location of the SAS data set, &DATA. %ODBCLOAD      */
   /*  takes the data from SASLIB.FROMSOGO and build       */
   /*  the generated SQL code in a file called             */
   /*  SQLCODE.SAS. This file is %INCLUDEd and it          */  
   /*  creates the Excel worksheet called FromSOGO.        */
libname saslib 'Novell:SAS data';
   
%odbcload(data=saslib.fromsogo, create=Y, table=FromSOGO)   
   
proc sql;   

      /*  Using "prompt" in the CONNECT statement above    */         
      /*  will prompt you for which ODBC driver to use.    */
      /*  The Excel version 4 driver is the correct        */
      /*  choice because it allows you to create an        */
      /*  Excel worksheet.                                 */
   connect to odbc as connect(prompt);   
   %inc sqlcode;
   disconnect from connect;
quit;