Return to Homepage

Goto the Tip of the Month Archive

Other interesting pages ...
LinkedIn Profile
SAS Cheat Sheet
Useful SAS Code
Full SAS Example
Basic Statistics
Contact Information

SAS Tip of the Month
February 2012
(for SAS and WPS)

Last month I showed how to break apart a dataset into seperate datasets using one of the values as a dataset name -- this month I am going to make it a little more general where you just have to use a macro, enter a dataset name and a variable.

Lets first slip the PROC SQL call into the macro BREAKUP:

   *A macro to actually do the subset;
   %macro breakup;
   
      *Get a unique list of PET values;
      proc sql noprint;
         select distinct pet 
            into :petval separated by " "
            from _AllPets;
         quit;
      run;
   
      *Initialize macro counter;
      %let i=1;
   
      *A DO loop to actually do the breakup -- read each value
       of PET contained in the macro variable PETVAL until no more;
      %do %while(%scan(&petval,&i) ne );
   
         *For each value of PET create a dataset with that VALUE,
          dropping PET from the outgoing dataset;
         data %scan(&petval,&i);
            set _AllPets;
            where pet="%scan(&petval,&i)";
            drop pet;
         run;
   
         *Increment the DO LOOP counter;
         %let i=%eval(&i+1);
   
      %end;
   %mend breakup;

It we run the macro with the same data then you will see the same results as last time. I may not look it, but we are actually just about there with a general macro that will break apart a dataset based on the values of a variable -- all we need to do is change all dataset references to a macro variable, and the same for variable references to a macro variable, as shown below:

   *A macro to actually do the subset;
   %macro breakup(dsn=  /*Dataset name*/
                 ,dsv=  /*Variable in dataset to use split by value*/
                 );
   
      *Get a unique list of values;
      proc sql noprint;
         select distinct &dsv 
            into :petval separated by " "
            from &dsn;
         quit;
      run;
   
      *Initialize macro counter;
      %let i=1;
   
      *A DO loop to actually do the breakup -- read each value
       inside in the macro variable PETVAL until no more;
      %do %while(%scan(&petval,&i) ne );
   
         data %scan(&petval,&i);
            set &dsn;
            where &dsv="%scan(&petval,&i)";
            drop &dsv;
         run;
   
         *Increment the DO LOOP counter;
         %let i=%eval(&i+1);
   
      %end;
   %mend breakup;

Now do the following call to the BREAKUP macro:

   %breakup(dsn=_allpet,dsv=pet);
   run;

Notice that the same datasets as previous are created but the code used to create the dataset now has what is called macro parameters, i.e. DSN= and DSV=. Now lets say our dataset had a hundred PET values with a thousand NAME values -- just call the same macro!

One important note here before you use the macro -- the values in the DSV parameter must not have spaces since the macro uses the spaces as delimiters between values in the macro variable PETVAL. So how can we change the macro again to handle this issue -- three lines need to be changed. Lets look first at the line:

   into :petval separated by " "

If we were to change the line to

   into :petval separated by "~"

then each value found in the variable given by the parameter DSV would be separated by a "~", so when scanning the macro variable in, then the two other lines that need to change are:

   data %scan(&petval,&i);
      where pet="%scan(&petval,&i)";

to read

   data %scan(&petval,&i,%str(~));
      where pet="%scan(&petval,&i,%str(~))";

So can we put this functionality into our macro -- short answer YES with one futher macro parameter:

   *A macro to actually do the subset;
   %macro breakup(dsn=         /*Dataset name*/
                 ,dsv=         /*Variable in dataset to use split by value*/
                 ,vrs=%str( )  /*Values seperated in list by character -- default is a space*/
                 );
   
      *Get a unique list of values;
      proc sql noprint;
         select distinct &dsv 
            into :petval separated by "&vrs"
            from &dsn;
         quit;
      run;
   
      *Initialize macro counter;
      %let i=1;
   
      *A DO loop to actually do the breakup -- read each value
       inside in the macro variable PETVAL until no more;
      %do %while(%scan(&petval,&i,&vrs) ne );
   
         data %scan(&petval,&i,"&vrs");
            set &dsn;
            where &dsv="%scan(&petval,&i,"&vrs")";
            drop &dsv;
         run;
   
         *Increment the DO LOOP counter;
         %let i=%eval(&i+1);
   
      %end;
   %mend breakup;

Now take this and try it on some data -- edit the code and see what happens. Remember to have the MPRINT, MLOGIC and SYMBOLGEN options on.

See you next month.

________________________________
Updated February 8, 2012