How to mean values by a variable within sql

[linlin87]

Hi SAS Community,

I have a numeric variable (var1) and I am trying to compute the mean of this value based on two variables - subjectID and siteID - but only for specific values of a third variable, test_run.

In particular, I only want to calculate the mean of var1 for test_run = 1-5. I want to do this for each subject and each site. I have a list of subjects (&sb) and sites (&site).

So far I have this:

proc sql;
create table dataset2 as
select *
from dataset1
where subjectid in (&sb) and site in (&site) and 1 le test_run le 5;
quit;

proc sort data=dataset2;
by subjectid site;
run;

proc means data=dataset2;
by subjectid site;
var var1;
output out=output1;
run;

However these two steps take a very long time. I was wondering if there was a way of calculating this mean of var1 for 1<=testrun<=5 in the initial SQL call?

[andreas_lds]

---

@linlin87 wrote:

....

However these two steps take a very long time. I was wondering if there was a way of calculating this mean of var1 for 1<=testrun<=5 in the initial SQL call?

---

"very long time" can mean anything, depending on the size of the data and the hardware, this could run some time. Please post the complete log of the steps shown with option fullstimer active.

[linlin87]

I would post the log, but 9/10 times SAS ends up crashing

[PaigeMiller]

How large is the data set?

[Tom]

Do you really need dataset2? Do you really need PROC MEANS to make a printout if you are already making a dataset?

Assuming that test_run only has integer values use IN operator for it also might be faster (depending on how DATASET1 is stored).

Try doing it in one step instead of three.

proc means data=dataset1 NOPRINT nway;
  where subjectid in (&sb)
    and site in (&site)
    and test_run in (1 2 3 4 5)
  ;
  class subjectid site;
  var var1;
  output out=output1;
run;

[linlin87]

Yes it does only have integer values. Thanks Tom I will try this and let you know how it goes

[Kurt_Bremser]

Try this:

proc means data=dataset1(where=(subjectid in (&sb) and site in (&site) and 1 le test_run le 5)) nway;
class subjectid site;
var var1;
output out=output1;
run;