## Macro variable and group by statements

Occasional Contributor
Posts: 9

# Macro variable and group by statements

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.
Regular Contributor
Posts: 241

## Re: Macro variable and group by statements

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

*/
Discussion stats