Advanced Search

Create a new data set for each BY-Group in a data set

/ Published in: SAS
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. /*Sample 2640 of the SAS Knowledge Base*/
  2. /*http://support.sas.com/kb/26/140.html*/
  3.  
  4. /* Example 1 - Use macro logic to create a new data set for each BY-Group in */
  5. /*             an existing data set.                                         */
  6.  
  7. /* Create sample data */
  8.  
  9. data test;
  10.   input color $ num;
  11. datalines;
  12. blue 1
  13. blue 2
  14. blue 3
  15. green 4
  16. green 5
  17. red 6
  18. red 7
  19. red 8
  20. ;
  21.  
  22. /* Create a new macro variable, VARn, for each BY-Group and a */
  23. /* counter of the number of new macro variables created.      */
  24.  
  25. data _null_;
  26.   set test end=eof;
  27.   by color;
  28.   /* On the first member of the BY-Group, create a new macro variable VARn */ 
  29.   /* and increment the counter FLAG.                                       */
  30.   if first.color then do;
  31.    flag+1;
  32.    call symput('var'||put(flag,8. -L),color);
  33.   end;
  34.   /* On the last observation of the data set, create a macro variable to */
  35.   /* contain the final value of FLAG.                                    */
  36.   if eof then call symput('tot',put(flag,8. -L));
  37. run;
  38.  
  39. /* Create a macro to generate the new data sets. Dynamically produce data set names */
  40. /* on the DATA statement, using subsetting criteria to create the new data sets     */
  41. /* based upon the value of the BY variable.                                         */                                          
  42.  
  43. %macro groups(dsn,byvar);
  44.   data %do i=1 %to &tot;
  45.         &&var&i
  46.        %end;;
  47.     set &dsn;
  48.       %do i=1 %to &tot;
  49.         if &byvar="&&var&i" then output &&var&i;
  50.       %end;
  51.   run;
  52. %mend groups;
  53.  
  54. /* Call the macro GROUPS.  Specify the name of the data set to be split */
  55. /* in the first macro parameter and the name of the BY variable in the  */
  56. /* second parameter.                                                    */
  57.  
  58. %groups(test,color)
  59.  
  60.  
  61. proc print data=blue;
  62.   title 'Blue';
  63. run;
  64.  
  65. proc print data=green;
  66.   title 'Green';
  67. run;
  68.  
  69. proc print data=red;
  70.   title 'Red';
  71. run;
  72.  
  73. /* Example 2 - Use CALL EXECUTE to pass a parameter to a macro in order to */
  74. /*             create a new data set for each BY-Group in an existing data */
  75. /*             set.  The output is identical to the output created by      */
  76. /*             Example 1 above.                                            */
  77.  
  78. /* Compile the macro BREAK.  The parameter BYVAL will be generated below in */
  79. /* the CALL EXECUTE.                                                        */
  80.  
  81. %macro break(byval);                                                                                                                                  
  82.    data &byval;                                                             
  83.       set test(where=(color="&byval"));                              
  84.    run;                                                                                                                                                 
  85. %mend;                                                                      
  86.  
  87. /* Use the same TEST data set created for Example 1. */ 
  88.  
  89. data _null_;                                                                
  90.   set test;                                                               
  91.   by color;                                                                  
  92.   if first.color then 
  93.     call execute(%nrstr('%break('||trim(color)||')'));                
  94.  
  95. run;

URL: http://jaredprins.squarespace.com/blog/2008/5/20/use-the-by-statement-to-create-a-new-data-set-for-each.html

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.