Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Dynamically select variables on which compute the mean

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 04-24-2020 10:10 AM
(556 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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);`

--

Paige Miller

Paige Miller

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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);`

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you !!!!

That's perfect !

Gluttony

That's perfect !

Gluttony

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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.

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--

Paige Miller

Paige Miller

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.