%macro utl_splitit
     (
       utl_title=Split a large table into many small tables based on a columns value,
       utl_obj=utl_splitit,

       /* INPUTS */

       utl_inlib01=d:\obj,
       utl_intbl0101=states,

       /* SPLIT VARIABLE */

       utl_invar01=State,

       /* OUTPUT */

       utl_otlib01=d:\obj,
       utl_ottbls=CA VT NY etc based on state variable in parent table,

       utl_flowpoint=0101010

     )
     / des = "Split a large table into many small tables based on a columns value";


   /*----------------------------------------------*\
   |                                                |
   |                                                |
   | The Problem                                    |
   |                                                |
   |                                                |
   | Given Table                                    |
   |                                                |
   |      STATES                                    |
   |                                                |
   |    State   Counties                            |
   |     CA       101                               |
   |     CA       102                               |
   |     CA       150                               |
   |     VT       101                               |
   |     VT       102                               |
   |     NY       101                               |
   |     NY       102                               |
   |     NY       103                               |
   |                                                |
   |                                                |
   | Create tables                                  |
   |                                                |
   |      CA                                        |
   |  CA       101                                  |
   |  CA       102                                  |
   |  CA       150                                  |
   |                                                |
   |                                                |
   |      VT                                        |
   |  VT       101                                  |
   |  VT       102                                  |
   |                                                |
   |      NY                                        |
   |  NY       101                                  |
   |  NY       102                                  |
   |  NY       103                                  |
   |                                                |
   |                                                |
   |                                                |
   |                                                |
   \*----------------------------------------------*/



   /*----------------------------------------------*\
   |                                                |
   |  Code by Roger DeAngelis and SAS-L             |
   |                                                |
   |  Compucraft Inc                                |
   |  49 Spackenkill Rd                             |
   |  Poughkeepsie, NY 12603                        |
   |                                                |
   |  Office 914-463-2770                           |
   |  Fax    914-462-7595                           |
   |                                                |
   |  Email                                         |
   |        xlr82sas@aol.com                        |
   |                                                |
   |  Users are free to do whatever they want       |
   |  with any or all of this code.                 |
   |                                                |
   |  Compucraft is not responsible for any         |
   |  problems associated with this code.           |
   |                                                |
   |  Use at your own risk                          |
   |                                                |
   \*----------------------------------------------*/




libname inlib01 "&utl_inlib01";
libname otlib01 "&utl_otlib01";

%local
       utl_Number_Levels
       utl_Left_Number_Levels
       utl_col
       utl_stindex
;

PROC SQL
            NOPRINT;

    SELECT
            COUNT(DISTINCT &utl_invar01 ) INTO :utl_Number_Levels

    FROM
            inlib01.&utl_intbl0101
    ;

    %let utl_Left_Number_Levels=&utl_Number_Levels;

    SELECT
            DISTINCT(&utl_invar01) INTO :utl_col1 - :utl_col&utl_left_Number_Levels

    FROM
            inlib01.&utl_intbl0101

    ;

  %do utl_stindex=1 %to &utl_Number_Levels;

    create
            table otlib01.&&utl_col&utl_stindex as

    select
            *

    from
            inlib01.&utl_intbl0101

    where
            &utl_invar01 = "&&utl_col&utl_stindex"

    ;

  %end;

quit;
run;




%mend utl_splitit;

/*------------------------*\
|   SAMPLE DATA            |
\*------------------------*/

data "d:\obj\states"
   (
    index= ( state )
   );

   do state='CA', 'VT', 'NY', 'PA';

      do counties=101 to 150 by 1;

         output;

      end;

   end;

run;

%utl_splitit;