• Print |
• E-mail  |
• Bookmark

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.

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

/* 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;

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

Operating System and Release Information