This sample uses macro logic to determine the number of unique values of a variable (the BY variable) and creates a new data set for each. The resulting data set names will be the BY variable value.

Limitations:

The sample code does not allow for BY values of longer than 32 positions, numeric BY values or BY values that contain characters that are not permitted in SAS data set names.

If your data contains any of the above, you must add program statements to convert your BY values into valid SAS data set names.

If you would like to use hash tables to accomplish this task, see EXAMPLE 5: OUTPUT N DATASETS. Click on the link below.

Paper 029-2008 How Do I Love Hash Tables Let Me Count The Ways! by Judy Loren .

If you have any questions on her paper, the contact information is at the end of the paper.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

/* Example 1 - Use macro logic to create a new data set for each BY-Group in */
/*             an existing data set.                                         */

/* Create sample data */

data test;
  input color $ num;
datalines;
blue 1
blue 2
blue 3
green 4
green 5
red 6
red 7
red 8
;

/* Create a new macro variable, VARn, for each BY-Group and a */
/* counter of the number of new macro variables created.      */

data _null_;
  set test end=eof;
  by color;
  /* On the first member of the BY-Group, create a new macro variable VARn */ 
  /* and increment the counter FLAG.                                       */
  if first.color then do;
   flag+1;
   call symput('var'||put(flag,8. -L),color);
  end;
  /* On the last observation of the data set, create a macro variable to */
  /* contain the final value of FLAG.                                    */
  if eof then call symput('tot',put(flag,8. -L));
run;

/* Create a macro to generate the new data sets. Dynamically produce data set names */
/* on the DATA statement, using subsetting criteria to create the new data sets     */
/* based upon the value of the BY variable.                                         */                                          

%macro groups(dsn,byvar);
  data %do i=1 %to &tot;
         &&var&i
       %end;;
    set &dsn;
      %do i=1 %to &tot;
        if &byvar="&&var&i" then output &&var&i;
      %end;
  run;
%mend groups;

/* Call the macro GROUPS.  Specify the name of the data set to be split */
/* in the first macro parameter and the name of the BY variable in the  */
/* second parameter.                                                    */

%groups(test,color)


proc print data=blue;
  title 'Blue';
run;

proc print data=green;
  title 'Green';
run;

proc print data=red;
  title 'Red';
run;

/* Example 2 - Use CALL EXECUTE to pass a parameter to a macro in order to */
/*             create a new data set for each BY-Group in an existing data */
/*             set.  The output is identical to the output created by      */
/*             Example 1 above.                                            */

/* Compile the macro BREAK.  The parameter BYVAL will be generated below in */
/* the CALL EXECUTE.                                                        */

%macro break(byval);                                                                                                                                  
   data &byval;                                                             
      set test(where=(color="&byval"));                              
   run;                                                                                                                                                 
%mend;                                                                      
                                                                   
/* Use the same TEST data set created for Example 1. */ 
                                                                                                                                                     
data _null_;                                                                
  set test;                                                               
  by color;                                                                  
  if first.color then 
    call execute('%break('||trim(color)||')');                
           
run;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Blue

Obs    color    num

 1     blue      1
 2     blue      2
 3     blue      3



Green            

Obs    color    num

 1     green     4
 2     green     5



Red                                           

Obs    color    num

 1      red      6
 2      red      7
 3      red      8

Create multiple SAS data sets from one SAS data set based upon the value of the BY variable.

Type:	Sample
Topic:	SAS Reference ==> Statements ==> File-handling ==> SET SAS Reference ==> DATA Step SAS Reference ==> Macro Data Management ==> Manipulation and Transformation ==> BY-group processing SAS Reference ==> Component Objects ==> hash object

Date Modified:	2009-09-04 11:30:10
Date Created:	2007-02-14 03:04:16

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	All	8.2 TS2M0	n/a