Help using Base SAS procedures

proc sql: average of multiple variables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 199
Accepted Solution

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: 7,492

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;

View solution in original post


All Replies
PROC Star
Posts: 7,492

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: 199

proc sql: average of multiple variables

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

PROC Star
Posts: 7,492

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,047

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: 199

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: 7,492

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: 7,492

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,047

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
  • 3620 views
  • 3 likes
  • 4 in conversation