topic Re: How to mean values by a variable within sql in New SAS User

How to mean values by a variable within sql

linlin87 — Wed, 22 Jun 2022 13:53:30 GMT

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?

Re: How to mean values by a variable within sql

andreas_lds — Wed, 22 Jun 2022 14:06:13 GMT

@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.

Re: How to mean values by a variable within sql

linlin87 — Wed, 22 Jun 2022 14:07:57 GMT

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

Re: How to mean values by a variable within sql

PaigeMiller — Wed, 22 Jun 2022 14:16:35 GMT

How large is the data set?

Re: How to mean values by a variable within sql

Tom — Wed, 22 Jun 2022 14:22:23 GMT

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;

Re: How to mean values by a variable within sql

Kurt_Bremser — Wed, 22 Jun 2022 14:47:41 GMT

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;

Re: How to mean values by a variable within sql

linlin87 — Wed, 22 Jun 2022 14:49:23 GMT

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