topic Re: Dynamically select variables on which compute the mean in SAS Programming

Dynamically select variables on which compute the mean

Gluttony — Fri, 24 Apr 2020 14:10:22 GMT

Hi,

I have a macro which creates a dataset which size depends on the input :

Columns names : a_1, a_2, a_3, a_4 ... .

I want to compute the mean of all the columns but the first two in the macro :

DATA want;
	SET have;
	mean_value=mean(a_3, a_4 ....);
RUN;

How can I dynamically select the columns ? (I know all the columns' name)

Maybe with columns' index/position (Mean on the 3rd column to the last one) ?

Gluttony

Re: Dynamically select variables on which compute the mean

PaigeMiller — Fri, 24 Apr 2020 14:14:00 GMT

Well, if the variables of interest all begin with a_ then you can use something like this:

mean_value = mean(of a_3-a_19);

Re: Dynamically select variables on which compute the mean

Gluttony — Fri, 24 Apr 2020 14:15:48 GMT

Thank you !!!!
That's perfect !
Gluttony

Re: Dynamically select variables on which compute the mean

ballardw — Fri, 24 Apr 2020 14:29:16 GMT

Is your issue that you do not know how many a_ variables there are and need help determining that?

Will you guarantee that there will always be 3 or more values?

Then perhaps:

data want;
   set have;
   array aa a_: ;
   mean_value = (sum (of aa(*)) - sum(a_1,a_2))/(n (of aa(*)) - 2);
run;

but you would need to check how many values are in the AA array if you can't be sure there are always 3 or more.

Re: Dynamically select variables on which compute the mean

PaigeMiller — Fri, 24 Apr 2020 14:36:31 GMT

@ballardw wrote:

Is your issue that you do not know how many a_ variables there are and need help determining that?

Will you guarantee that there will always be 3 or more values?

Then perhaps:
data want;
   set have;
   array aa a_: ;
   mean_value = (sum (of aa(*)) - sum(a_1,a_2))/(n (of aa(*)) - 2);
run;
but you would need to check how many values are in the AA array if you can't be sure there are always 3 or more.

Doesn't work if a_1 and/or a_2 is missing, the denominator will be wrong.

Re: Dynamically select variables on which compute the mean

Gluttony — Fri, 24 Apr 2020 14:54:41 GMT

In my case, the columns were :

a_1, a_2, b_1, b_2, b_3 .... b_n

and I wanted to compute the mean on the "b" columns,

So @PaigeMiller's trick (before the edit) about using :

mean(of b:)

is perfect.
Thank you,

Gluttony

Re: Dynamically select variables on which compute the mean

PaigeMiller — Fri, 24 Apr 2020 14:54:38 GMT

as long as there are no other variables whose name starts with B in your data set, that would work. For example, if your data set also contains variable BirthYear then the B: in the MEAN function won't work.

Re: Dynamically select variables on which compute the mean

ballardw — Fri, 24 Apr 2020 14:56:49 GMT

@PaigeMiller wrote:
@ballardw wrote:

Is your issue that you do not know how many a_ variables there are and need help determining that?

Will you guarantee that there will always be 3 or more values?

Then perhaps:
data want;
   set have;
   array aa a_: ;
   mean_value = (sum (of aa(*)) - sum(a_1,a_2))/(n (of aa(*)) - 2);
run;
but you would need to check how many values are in the AA array if you can't be sure there are always 3 or more.
Doesn't work if a_1 and/or a_2 is missing, the denominator will be wrong.

Yep.

Incomplete data description => incomplete response.