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;
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;
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?
I only want to include non-missing values in the denominator.
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;
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
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.
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;
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
Linlin: the OP requested a SQL solution
Hi Art,
Thank you for reminding me. I was eager to practice the DOW technique learned from the paper you recommended yesterday.:smileysilly:
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.