Macro variable and group by statements

rickw — Mon, 28 Feb 2011 17:34:32 GMT

Hi,

I am using strings entered as macro variable to run the by statements:

% let string = a b c d e;

proc sql;
create table temp as
select &string, Sum(x), avg(y) from x
group by &string.
;
quit;

In this case the result I want are the combination of a b c d e groupings ...

That is one result should have a= All and calculate group by as b c d e
then calculate group by using b = All and group by a c d e
then calculate group by using a = All and b = All group by c d e
Similarly results when a = All , b = All , C = All so on ...

Basically I want to get all possible combinations I could get with these five group by variables in the string ....

What would be the best way to approach something like this??

Thanks.

Re: Macro variable and group by statements

chang_y_chung_hotmail_com — Mon, 28 Feb 2011 19:10:49 GMT

Proc means can do this in one step. No macros are needed.

   /* test data */

   proc plan see=1234567;

      factors rep=3 a=2 b=3 c=2 /noprint;

      treatments x=12;

      output out=one;

   run;

   /* calculate sum and avg of x, all ways */

   proc means data=one;

     class a b c;

     var x;

     ways 0 to 3;

     output out=two sum=sum mean=avg;

   run;

   proc print data=two;

   run;

   /* on log

   Obs    a    b    c    _TYPE_    _FREQ_    sum      avg

     1    .    .    .       0        36      219    6.08333

     2    .    .    1       1        18      122    6.77778

     3    .    .    2       1        18       97    5.38889

     4    .    1    .       2        12       76    6.33333

     5    .    2    .       2        12       69    5.75000

     6    .    3    .       2        12       74    6.16667

     7    .    1    1       3         6       50    8.33333

     8    .    1    2       3         6       26    4.33333

     9    .    2    1       3         6       45    7.50000

    10    .    2    2       3         6       24    4.00000

    11    .    3    1       3         6       27    4.50000

    12    .    3    2       3         6       47    7.83333

    13    1    .    .       4        18      101    5.61111

    14    2    .    .       4        18      118    6.55556

    15    1    .    1       5         9       61    6.77778

    16    1    .    2       5         9       40    4.44444

    17    2    .    1       5         9       61    6.77778

    18    2    .    2       5         9       57    6.33333

    19    1    1    .       6         6       36    6.00000

    20    1    2    .       6         6       27    4.50000

    21    1    3    .       6         6       38    6.33333

    22    2    1    .       6         6       40    6.66667

    23    2    2    .       6         6       42    7.00000

    24    2    3    .       6         6       36    6.00000

    25    1    1    1       7         3       29    9.66667

    26    1    1    2       7         3        7    2.33333

    27    1    2    1       7         3       19    6.33333

    28    1    2    2       7         3        8    2.66667

    29    1    3    1       7         3       13    4.33333

    30    1    3    2       7         3       25    8.33333

    31    2    1    1       7         3       21    7.00000

    32    2    1    2       7         3       19    6.33333

    33    2    2    1       7         3       26    8.66667

    34    2    2    2       7         3       16    5.33333

    35    2    3    1       7         3       14    4.66667

    36    2    3    2       7         3       22    7.33333

   */

topic Re: Macro variable and group by statements in SAS Programming

Macro variable and group by statements

Re: Macro variable and group by statements