## proc sql: average of multiple variables

Solved
Regular Contributor
Posts: 209

# proc sql: average of multiple variables

Using proc sql how do I create one variable with the average of 30 numeric variables (i.e. x1-x30)?

Currently, I'm using this code to find the average of one variable:

proc sql;

CREATE TABLE survey3 as

SELECT x,jobgroup,

round(avg(q19),0.01) as q19_avg,

count(q19) as q19_n

FROM survey2

group by x,jobgroup;

QUIT;

RUN;

Accepted Solutions
Solution
‎02-08-2012 03:01 PM
PROC Star
Posts: 8,164

## proc sql: average of multiple variables

Using the test data that Ksharp provided you would only have to modify the code I originally suggested as follows:

data x;

input q1-q4;

cards;

1 2 3 4

2 4 5 6

36 6 8 97

23 45 65 78

;

proc sql noprint;

select 'sum('||trim(name)||') ',

'count('||trim(name)||') '

into :vars separated by '+',

:counts separated by '+'

from dictionary.columns

where libname='WORK' and

memname='X' and

name like 'q%';

;

create table want as

select &vars. as sums,

&counts. as counts,

calculated sums/calculated counts as averages

from x

;

quit;

All Replies
PROC Star
Posts: 8,164

## proc sql: average of multiple variables

How do you want to calculate the average?  If you have 30 variables and 2 records is it the total of all 60 values divided by 60 or are only non-missing values supposed to be considered in the denominator?

Regular Contributor
Posts: 209

## proc sql: average of multiple variables

I only want to include non-missing values in the denominator.

PROC Star
Posts: 8,164

## proc sql: average of multiple variables

I think this is what you are trying to accomplish.  I added a comparison example doing the same thing with proc means:

data testcase;

set sashelp.class;

if age eq 15 then call missing(weight);

run;

data test(keep=sex var);;

set testcase;

array vars age--weight;

do over vars;

var=vars;

output;

end;

run;

proc means data=test;

var var;

class sex;

run;

proc sql noprint;

select 'sum('||trim(name)||') ',

'count('||trim(name)||') '

into :vars separated by '+',

:counts separated by '+'

from dictionary.columns

where libname='WORK' and

memname='TESTCASE' and

type='num'

;

create table testsql as

select sex,&vars. as sums,

&counts. as counts,

calculated sums/calculated counts as averages

from testcase

group by sex

;

quit;

proc print;

run;

Super User
Posts: 10,784

## proc sql: average of multiple variables

Or

```data x;
input q1-q4;
cards;
1 2 3 4
2 4 5 6
36 6 8 97
23 45 65 78
;run;

proc sql ;
select name into : list separated by ','
from dictionary.columns
where libname='WORK' and memname='X' and name like 'q%';

select mean(&list) as mean from x;
quit;

```

Ksharp

Regular Contributor
Posts: 209

## proc sql: average of multiple variables

This is good, but I need the new variable with the average to be equal to 24.0625 (overall average of all values) instead of 2.5, 4.25, 36.75, 52.75.

Solution
‎02-08-2012 03:01 PM
PROC Star
Posts: 8,164

## proc sql: average of multiple variables

Using the test data that Ksharp provided you would only have to modify the code I originally suggested as follows:

data x;

input q1-q4;

cards;

1 2 3 4

2 4 5 6

36 6 8 97

23 45 65 78

;

proc sql noprint;

select 'sum('||trim(name)||') ',

'count('||trim(name)||') '

into :vars separated by '+',

:counts separated by '+'

from dictionary.columns

where libname='WORK' and

memname='X' and

name like 'q%';

;

create table want as

select &vars. as sums,

&counts. as counts,

calculated sums/calculated counts as averages

from x

;

quit;

Super Contributor
Posts: 1,636

## Re: proc sql: average of multiple variables

How about the code below?

data x;

input q1-q4;

cards;

1 2 3 4

2 4 5 6

36 6 8 97

23 45 65 78

;run;

data want (keep=ave total n);

do until (last);

set x end=last;

array q(*) q:;

do _n_=1 to dim(q);

if q(_n_) ne . then do;

total+q(_n_);

n+1;

end;

end;

end;

ave=total/n;

run;

proc print;run;

Obs    total     n      ave

1     385     16    24.0625

/* if you have two missing values */

data x_missing;

input q1-q4;

cards;

. 2 3 4

. 4 5 6

36 6 8 97

23 45 65 78

;run;

data want_missing (keep=ave total n);

do until (last);

set x_missing end=last;

array q(*) q:;

do _n_=1 to dim(q);

if q(_n_) ne . then do;

total+q(_n_);

n+1;

end;

end;

end;

ave=total/n;

run;

proc print;run;

Obs    total     n      ave

1      382     14    27.2857

Linlin

Message was edited by: Yulin Liu

Message was edited by: Yulin Liu

PROC Star
Posts: 8,164

## Re: proc sql: average of multiple variables

Linlin: the OP requested a SQL solution

Super Contributor
Posts: 1,636

## Re: proc sql: average of multiple variables

Hi Art,

Thank you for reminding me. I was eager to practice the DOW technique learned from the paper you recommended yesterday.:smileysilly:

Super User
Posts: 10,784

## Re: proc sql: average of multiple variables

OK. How About:

```data x;
input q1-q4;
cards;
1 2 3 4
2 4 5 6
36 6 8 97
23 45 65 78
;run;

proc sql ;
select name into : list separated by ','
from dictionary.columns
where libname='WORK' and memname='X' and name like 'q%';

select mean(mean(&list)) as mean from x;
quit;

Ksharp
```
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 10 replies
• 4550 views
• 3 likes
• 4 in conversation