Hi All,
I have a lot of variables in my dataset.
variables in my dataset:
id w1 w2 w3 h1 h2 h3 age1 age2 ag3 .... plus a lot other variables ...rate1 rate2 rat3.
I need to calculate means of:
mean_w=mean(of,w1-w3);
mean_h=mean(of,h1-h3);
...
mean_rate=mean(of,rate1-rate3);
what is the best way to do it?
Thanks!
Slight modification of my previous suggestion to account for variable ranges that go beyond 1 digit. I think this is a lot more direct than dealing with arrays in this case as the functions can deal directly with a variable prefix:
data have;
input id $ this1-this3 that1-that3 x1-x10 y;
cards;
1 1 2 1 4 3 2 5 6 7 1 2 3 4 5 6 7 9
2 5 6 7 3 2 4 8 6 7 1 2 3 4 5 6 7 9
;
proc sql noprint;
select "mean_"||
strip(name)||
"=mean(of "||
strip(name)||
":);"
into :getmeans separated by " "
from (select distinct substr(name,1,length(name)-
anyalpha(reverse(strip(name)))+1) as name
from dictionary.columns
where libname="WORK" and
memname="HAVE" and
type="num" and
anydigit(substr(name,length(name),1))
)
;
quit;
data want;
set have;
&getmeans.;
run;
Other than restructuring that data so that the repetitions are rows?
How about arrays?
data have;
input id w1-w3 h1-h3 ;
cards;
1 1 2 3 10 11 12
2 4 5 6 13 14 15
run;
data want;
set have;
array reps (2,3) w1--h3;
array means mean_w mean_h ;
do i=1 to dim(means);
means(i) = mean(of reps(i,1),reps(i,2),reps(i,3));
end;
run;
Hi Tom,
I like your array solution.
Since the OP stated there are a lot of variables, putting the variables into macro variables may save some typing:
data have;
input id w1-w3 h1-h3 ag1-ag3 rate1-rate3;
cards;
1 1 2 3 10 11 12 20 21 23 90 91 92
2 4 5 6 13 14 15 20 21 23 90 . 92
;
run;
proc contents data=have out=vars(keep=name varnum ) noprint; run;
proc sql noprint ;
select cats('mean_',substr(name,1,length(name)-1)) into : names separated by ' '
from vars
where upcase(name) ne 'ID' and substr(name,length(name))='3'
order by varnum;
select name into : allvar separated by ' '
from vars
where substr(name,length(name)) in ('1','2','3')
order by varnum;
quit;
%let n=%sysfunc(countw(&names));
data want;
set have;
array reps (&n,3) &allvar;
array means &names ;
do i=1 to dim(means);
means(i) = mean(of reps(i,1),reps(i,2),reps(i,3));
end;
drop i;
run;
proc print;run;
If none of your variable ranges go beyond a single digit you might be able to do it with the following. However, you'd have to adjust the code if any go beyond a single digit:
data have;
input id $ this1-this3 that1-that3 x1-x3 y;
cards;
1 1 2 1 4 3 2 5 6 7 9
2 5 6 7 3 2 4 8 6 7 9
;
proc sql noprint;
select "mean_"||
strip(name)||
"=mean(of "||
strip(name)||
":);"
into :getmeans separated by " "
from (select distinct substr(name,1,length(name)-1) as name
from dictionary.columns
where libname="WORK" and
memname="HAVE" and
type="num" and
anydigit(substr(name,length(name),1))
)
;
quit;
data want;
set have;
&getmeans.;
run;
Slight modification of my previous suggestion to account for variable ranges that go beyond 1 digit. I think this is a lot more direct than dealing with arrays in this case as the functions can deal directly with a variable prefix:
data have;
input id $ this1-this3 that1-that3 x1-x10 y;
cards;
1 1 2 1 4 3 2 5 6 7 1 2 3 4 5 6 7 9
2 5 6 7 3 2 4 8 6 7 1 2 3 4 5 6 7 9
;
proc sql noprint;
select "mean_"||
strip(name)||
"=mean(of "||
strip(name)||
":);"
into :getmeans separated by " "
from (select distinct substr(name,1,length(name)-
anyalpha(reverse(strip(name)))+1) as name
from dictionary.columns
where libname="WORK" and
memname="HAVE" and
type="num" and
anydigit(substr(name,length(name),1))
)
;
quit;
data want;
set have;
&getmeans.;
run;
Hi Art,
If I want to see the value of your macro variable &getmeans and add "%put &getmeans;" to your code, is there a way to avoid the error messages in log file?
Thank you!
Thank you Art! - Linlin
All worked great. Thank you very much for your help!!!
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.