BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gzr2mz39
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

10 REPLIES 10
art297
Opal | Level 21

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
Quartz | Level 8

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

art297
Opal | Level 21

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
Super User

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
Quartz | Level 8

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
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Opal | Level 21

Linlin: the OP requested a SQL solution

Linlin
Lapis Lazuli | Level 10

Hi Art,

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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