topic Re: proc sql: average of multiple variables in SAS Procedures

proc sql: average of multiple variables

gzr2mz39 — Mon, 06 Feb 2012 21:41:42 GMT

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;

proc sql: average of multiple variables

art297 — Mon, 06 Feb 2012 23:21:04 GMT

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?

proc sql: average of multiple variables

gzr2mz39 — Mon, 06 Feb 2012 23:27:17 GMT

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

proc sql: average of multiple variables

art297 — Tue, 07 Feb 2012 00:55:58 GMT

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;

proc sql: average of multiple variables

Ksharp — Wed, 08 Feb 2012 09:17:35 GMT

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

proc sql: average of multiple variables

gzr2mz39 — Wed, 08 Feb 2012 19:46:02 GMT

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.

proc sql: average of multiple variables

art297 — Wed, 08 Feb 2012 20:01:38 GMT

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;

Re: proc sql: average of multiple variables

Linlin — Wed, 08 Feb 2012 20:04:42 GMT

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;

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;

ave=total/n;

run;

proc print;run;

Obs total n ave

1 382 14 27.2857

Linlin

Message was edited by: Yulin Liu

Re: proc sql: average of multiple variables

art297 — Wed, 08 Feb 2012 20:17:21 GMT

Linlin: the OP requested a SQL solution

Re: proc sql: average of multiple variables

Linlin — Wed, 08 Feb 2012 20:25:11 GMT

Hi Art,

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

Re: proc sql: average of multiple variables

Ksharp — Fri, 10 Feb 2012 09:08:56 GMT

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