proc sql: average of multiple variables

[gzr2mz39]

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;

[art297]

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?

[gzr2mz39]

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

[art297]

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;

[Ksharp]

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

[gzr2mz39]

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.

[art297 · Accepted solution]

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;

[Linlin]

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

[art297]

Linlin: the OP requested a SQL solution

[Linlin]

Hi Art,

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

[Ksharp]

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